SQL Server Performance Forum – Threads Archive

Help on SUBSTRING !

I need to update (remove) some data from a column which contains special character. For instance, a column called DESCRIPTION contains: ‘MAIN || This is valid text’. I need to remove MAIN || from the column and keep the text after ||. Basically, I need to check the data in this column and wherever there is ||, I will need to remove text before this. I am working on it but if anyone can come up with quick solution, it will be really great. Thanks in advance. Thanks, Name
———
Dilli Grg (1 row(s) affected)

Not so terriby difficult, as long as you understand the SUBSTRING and CHARINDEX functions … UPDATE MyTable
SET Description = SUBSTRING(Description, CHARINDEX(‘||’, Description) + 2, 100)
WHERE Description LIKE ‘%||%’ The second parameter for the SUBSTRING function is the starting point, which is two positions beyond where the || string starts.
The third parameter for the SUBSTRING function is how many remaining characters there will be. I’ve chosen 100 here, but you can take a larger number if you need one.

quote:Originally posted by Adriaan Not so terriby difficult, as long as you understand the SUBSTRING and CHARINDEX functions … UPDATE MyTable
SET Description = SUBSTRING(Description, CHARINDEX(‘||’, Description) + 2, 100)
WHERE Description LIKE ‘%||%’ The second parameter for the SUBSTRING function is the starting point, which is two positions beyond where the || string starts.
The third parameter for the SUBSTRING function is how many remaining characters there will be. I’ve chosen 100 here, but you can take a larger number if you need one.

Adriaan, that was very quick. Really appreciated. Now, Can this be done in one script if there are other special characters in the same column. Let’s say ‘MAIN || This is valid ! text’ to ‘This is valid text’. Thanks, Name
———
Dilli Grg (1 row(s) affected)

Lets not complicate this. I will write separate script for this. Thanks, Name
———
Dilli Grg (1 row(s) affected)

Hi,
After charindex to consider the text after ||, you can use REPLACE function to replace ! with ”
and use few more replace to remove other special characters if still left.

You can add the REPLACE around the SUBSTRING expression.

That is, you can nest functions. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

That is a much nicer way of putting it.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Thanks All for your time/suggestion. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <pre id="code"> Thanks, Name ——— Dilli Grg (1 row(s) affected) </pre id="code">

]]>