Replace function with Like in function

Transact-SQL
1

I'm having a hard time with a replace. I want to replace text where it begins with " and replace that with nothing. From there I'm going to build on this with more replace functions. So I don't think a where clause will work

select f1,
replace(f2, like '"%', ''),
f3
from road_table

sample f2
"RUBBER * ROLLER TIRE 6"" DIA."
"BOLT * HEX 3/8""-16 X 2 1/2"""

What I'm trying to achieve is RUBBER ROLLER TIRE 6in DIA, BOLT HEX 3/8in-16 X 2 1/2in

2

You would need to remove the First & Last characters where they are "

; WITH CTE1 AS
(
SELECT [f2]='"RUBBER * ROLLER TIRE 6"" DIA."'
UNION ALL SELECT '"BOLT * HEX 3/8""-16 X 2 1/2"'
)
, CTE2 AS
(
SELECT	[F2a] = CASE WHEN LEFT(f2, 1) ='"' THEN STUFF(f2, 1, 1, '') ELSE f2 END
FROM CTE1
)
SELECT [F2b] = CASE WHEN RIGHT(f2a, 1) ='"' THEN STUFF(f2a, LEN(f2a), 1, '') ELSE f2a END
FROM CTE2

Then you can REPLACE any remaining " with 'in' (although that may be risky, unless you are sure that ALL embedded " characters represent inches, rather than Quoted Names. For example, using quotes around Hybrid Names in the full Latin-name for plants, is common. Clearly your Rubber Rollers aren't Latin names! but maybe you have some things which are quoted within the text?

3

Alternative:

with cte(string)
  as (          select '"RUBBER * ROLLER TIRE 6"" DIA."'
      union all select '"BOLT * HEX 3/8""-16 X 2 1/2"""'
     )
select replace(case
                  when string like '"%"'
                  then substring(string,2,len(string)-2)
                  else string
               end
              ,'""'
              ,'in'
              )
  from cte
;
4

Assumes that there is always a " at both ends of the string. Seems like a reasonable assumption, so just raising it in case it might not be the case.

Mine is going to have a problem with

UNION ALL SELECT 'BOLT * HEX 3/8""-16 X 2 1/2""'

:slight_smile: