find and replace - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

find and replace

Author

Topic

kt
Yak Posting Veteran

88 Posts

Posted - 2013-04-29 : 14:01:48

Hi,

i have the string from the description like below. I want to find the first " and replace with empty.

"S40 12"" 316,316,17-4,RTFE/SI
i would like my final result shows below.
S40 12"" 316,316,17-4,RTFE/SI

thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-29 : 14:05:30

Use STUFF and PATINDEX functions like this:

DECLARE @x VARCHAR(255) = '"S40 12"" 316,316,17-4,RTFE/SI"'
SELECT STUFF(@x,PATINDEX('%"%',@x),1,'');

kt
Yak Posting Veteran

88 Posts

Posted - 2013-04-29 : 14:24:34

you are sooooooooooo good, thank you very much.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-29 : 17:21:13

You are very welcome - glad to be of help.

One thing you do want to be careful about is if you have rows where there are no double-quotes at all. In such cases, you would get incorrect results, unless you check for it before doing the substituition.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-30 : 04:40:33

this will make it still works for one without any " within it


DECLARE @x VARCHAR(255) = '"S40 12"" 316,316,17-4,RTFE/SI"'
SELECT STUFF(@x,PATINDEX('%"%',@x + '"'),1,'');

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources