SQL Server：仅在以alpha开头时替换特殊字符

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.

我试图创建一个函数，它在一个字符串，并替换[字母字符]的所有实例”用α-字符]英寸。例如，串4" 贴纸为4英寸的贴纸，但字符串我的‘爱’兄弟保持不变。

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?

我知道我可以使用REPLACE直接替换（@String，'“'，'Inch'），但这不会使第二个例子保持不变。如果前面的值是alpha（aZ），我怎么能替换这个特殊字符值？

I appreciate any assistance!

我感谢任何帮助！

2 个解决方案

#1

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

只是为了帮助Visual

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

子查询生成以下内容，只执行一系列“动态”替换

#2

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

您可以使用递归CTE执行此操作，如下所示：

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"

#1