How to replace first and last occurance from a string

Question

How to replace first and last occurance from a string

vkundar

Hall of Fame

Points: 3431
More actions
October 26, 2009 at 7:56 pm

#136889

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

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

vkundar Hall of Fame Points: 3431 More actions · Answer 2

Thanks a lot..saved my day.

Cheers,

Got an idea..share it !!

DBA_Vishal

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

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

Chris Howarth-536003 SSCommitted Points: 1775 More actions · Answer 4

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