October 26, 2009 at 7:56 pm
Hello everyone,
I am not able to replace the first and last occurance in a string.
In my table the subject column have data enclosed with double qotoes(")
My requirement is that the first and last double quotes should be replace not the other occurance in the string.
How should I do that, coz replace function will remove all the occurance of the serach string?:w00t:
Please help me out. Thank you all.
Cheers,
Got an idea..share it !!
DBA_Vishal
October 26, 2009 at 8:12 pm
This could be done in one statement, or with a function, but the CTEs make it a little easier to read. The first CTE uses the STUFF() function to remove the first " in the string, and then reverses the string. The second CTE uses STUFF to remove the first " in the reversed string (which is the last "), and then reverses it back to normal.
declare @sample table (testString varchar(100))
insert into @sample
select 'Look here: "This is a quote about "laissez faire" economics." See?'
select 'Before',* from @sample
;with cte1 (testString) as (select reverse(stuff(teststring,charindex('"',testString,1),1,'')) from @sample)
,cte2 (testString) as (select reverse(stuff(teststring,charindex('"',testString,1),1,'')) from cte1)
select 'After',* from cte2
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 27, 2009 at 1:00 pm
Thanks a lot..saved my day.
Cheers,
Got an idea..share it !!
DBA_Vishal
October 27, 2009 at 2:34 pm
You're welcome.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 28, 2009 at 2:03 pm
An alternative approach, if all of your strings contain one leading " and one trailing ", would be to use the SUBSTRING function:
DECLARE @MyString VARCHAR(100),
SET @MyString = '"1234567"'
SELECT SUBSTRING(@MyString, 2, LEN(@MyString) - 2)
...or:
SELECT SUBSTRING(MyColumn, 2, LEN(MyColumn) - 2)
FROM dbo.MyTable
Chris
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply