SQL Server: Replace special character only when preceded by alpha

1
votes

I'm attempting to create a function that takes in a string and replaces all instances of [alpha character]" with [alpha character] Inch. For instance, the string 4" sticker becomes 4 Inch sticker, but the string My "Loving" Brother remains unchanged.

I know I can replace directly using REPLACE(@String, '"', ' Inch'), but that wouldn't leave the second example unchanged. How can I replace this special character only when the preceding value is an alpha (a-Z) value?

I appreciate any assistance!

2
sqlsql-serversql-server-2008
"All Instances" so if you had 4"x2" sticker book nothing would change? So really, you only want to change the value if it occurs just once? Rule is too open for ambiguity in my book. Maybe... rextester.com/IFC94069 but it only handles 1 Case when len(src)-len(replace(src,'"','')) = 1 then replace(src,'"',' Inch ') else src end – xQbert
That's correct. It would definitely be nice to account for edge cases like your example, but I think if I understand how to accomplish the simple case, I can probably figure out how to build out the rest. I didn't want to waste anymore of your (or anyone else's) time than necessary. – AliceSmith

2 Answers

2
votes

Yet another option, which can easily be converted into a Table-Valued Function or Scalar-Valued Function

Example

Declare @S varchar(max) = 'The "bid" was for 8'' 12"'

Select @S = Replace(@S,MapFrom,MapTo)
 From (
        Select MapFrom = concat(n,MapFrom)
              ,MapTo   = concat(n,MapTo)
         From (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) N(N)  
         Cross Join (values ('''',' Feet'),('"',' Inch')) C(MapFrom,MapTo)          
      ) A

Select @S

Returns

The "bid" was for 8 Feet 12 Inch

Just to aid with the Visual

The subquery generates the following, which simply perform a series of "dynamic" replaces

enter image description here

1
votes

You can do it with a recursive CTE, like this:

declare @InputString varchar(100)
set @InputString = '2"x4" picture frame "Love"'
;with a as(select convert(varchar(max),@InputString) i, convert(int, PATINDEX('%[0-9]"%', @InputString)) p
union all 
select stuff(i, p+1, 1, ' Inch') i, convert(int, PATINDEX('%[0-9]"%', stuff(i, p+1, 1, ' Inch'))) p
from a where PATINDEX('%[0-9]"%', i) > 0)
select * from a
where p = 0

Results:

2 Inchx4 Inch picture frame "Love"