CODEWITHSUNDEEP
sqlsql-servert-sql
S. S. Rawat
S. S. Rawat

Reputation: 6111

Remove character from the string

I have a string, now I want to replace commas between the string. 

Declare @Query nvarchar(max)
Set @Query = 'Item1,Item2,"Item,Demo,3",New'

From the given string I want to remove comma from between double quotes("")

I want result like this 

'Item1,Item2,ItemDemo3,New'

Here this "Item,Demo,3" part now ItemDemo3

Upvotes: 0

Views: 111

Answers (1)

masum7
masum7

Reputation: 832

The idea is to use PATINDEX to find a pattern. Here I have used patindex to find double quote and then used start and length of the desired substring. There will be three portions: before, modified desired and after

Then I replaced the comma and made the string again

    Declare @Query nvarchar(max) 

    Set @Query = 'Item1,Item2,"Item,Demo,3",New'

    Declare @start int, @len int
    SELECT @start = PATINDEX('%"%"%', @Query) + 1

    select @len=CHARINDEX('"', SUBSTRING(@Query, @start, LEN(@Query))) - 1

    select 
        SUBSTRING(@Query, 1, @start - 2) +
        REPLACE((SUBSTRING(@Query, @start, @len)), ',', '') +
        SUBSTRING(@Query, @start + @len + 1, LEN(@Query))

Please let me know whether it works..

Upvotes: 1

Related Questions