CODEWITHSUNDEEP
sql-servert-sql
Ravi Ram
Ravi Ram

Reputation: 24488

Find RegExp and Replace specific character

I need to find a numeric value + quotes within a string (example 5"). Once found, I need to replace the quote with "pound". Unfortunately, the string is complex: Sample string

Work and run with Joe "The King" Mel using a 3" vest with "Dog-Bone" weights.

I have tried 

SELECT REPLACE(REPLACE('Work and run with Joe "The King" Mel using a 3" vest with "Dog-Bone" weights.', '%[^0-9]"%', 'pound'), '"', 'pound')

But it replaces all the quotes with 'pound'.

Upvotes: 1

Views: 326

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

This will find the first occurrence of a number followed by a " and replace " with pound. 

declare @s varchar(100) 
set @s = 'Work and run with Joe "The King" Mel using a 3" vest with "Dog-Bone" weights.'
select stuff(@s, patindex('%[0-9]"%', @s)+1, 1, ' pound')

STUFF

PATINDEX

If you have more than one you can put it in a while loop.

while patindex('%[0-9]"%', @s) > 0
begin
  set @s = stuff(@s, patindex('%[0-9]"%', @s)+1, 1, ' pound')
end

Upvotes: 4

lkurylo
lkurylo

Reputation: 1651

If you can use a CLR: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Upvotes: 0

bmargulies
bmargulies

Reputation: 100133

If you can't express a deterministic algorithm for the change you want to make, you'll never be able to write a regular expression as an implementation.

You'd written two replace expressions: one replaces only quotes after numbers, but the other replaces all the quotes, period.

Upvotes: 0

Related Questions