CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Russell Alan
Russell Alan

Reputation: 116

Replace Function - Handling single quotes and forward slashes in SQL SERVER 2008

I want to replace some data from my database where single quotes and slashes are present.

The line below is exactly how it appears in the database and I only want to remove 'F/D', from the record.

('P/P','F/D','DFC','DTP')

Been using varations of

UPDATE tablename SET columnname = REPLACE(columnname, '''F/D,''', '')
WHERE RECORDID = XXXXX

Also been using varations of

UPDATE tablename SET columnname = REPLACE(columnname, 'F/D,', '')
WHERE RECORDID = XXXXX

Seems like it should be a simple fix but I haven't had any luck yet - all suggestions are appreciated.

Upvotes: 1

Views: 112

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270883

Your first version should work fine if the comma is in the right place:

UPDATE tablename
    SET columnname = REPLACE(columnname, '''F/D'',', '')
    WHERE RECORDID = XXXXX;

Note that this will not replace 'F/D' if it is the first or last element in the value. If that is an issue, I would suggest that you ask another question.

Upvotes: 0

S3S
S3S

Reputation: 25152

The reason your's doesn't work is because you aren't including the quotes. You are looking for F/D, and 'F/D,' and your data it is 'F/D',.

If it's simply 'F/D' from all values you want removed, then you also need to remove a comma and the quotes. This method removes 'F/D' and then, any double commas (in case 'F/D' is in the middle of the string). 

declare @var varchar(64) = '(''P/P'',''F/D'',''DFC'',''DTP'')'

select replace(replace(@var,'''F/D''',''),',,',',')

--update tablename 
--set columnname = replace(replace(columnname,'''F/D''',''),',,',',')
--where RECORDID = 1324

If you want to replace the second element in the string, here is a way:

select 
        @var
         --find the location of the first comma
        ,charindex(',',@var,0)  
        --find the location of the second comma
        ,charindex(',',@var,charindex(',',@var) + 1)    
        --Put it all together, using STUFF to replace the values between this range with nothing
        ,stuff(@var,charindex(',',@var,0),charindex(',',@var,charindex(',',@var) + 1) - charindex(',',@var,0),'')

Upvotes: 1

Related Questions