SQL中的PATINDEX不能使用特殊字符 [英] PATINDEX IN SQL NOT WORK WITH SPECIAL CHARACTER

问题描述

如果在输入字符串中有'％'这样的sql字符，那么它不起作用那么解决方案是什么。

i使用替换但是不可行的解决方案因为替换了多少字符是一个问题。

it does not work if there is sql character like '%' in input string so what is the solution.
i used replace but its not feasible solution as how many character replaced is a question.

declare @phone varchar(50)
set @phone='212%@@f'

select  REPLACE(@phone
               , SUBSTRING(@phone, PATINDEX('%[^0-9a-zA-Z .()/-]%', @phone), 1)
              , '')

推荐答案

SQL server 2008现在正在编写代码但当输入中有单个cote时它会失败

SQL server 2008 now its work as i write code but when there is single cote in input it fails

WHILE(PATINDEX('%[^0-9a-zA-Z .()/-]%', @phone) ) > 0
   BEGIN
   --then remove that one character, then continue
   SET @phone = REPLACE(@phone
   , SUBSTRING(@phone, PATINDEX('%[^0-9a-zA-Z .()/-]%', @phone), 1)
   , '')
   END

我会稍微修改第一个答案..

I will slightly modify the first answer..

declare @phone varchar(50)
set @phone='212%@@f'

WHILE(PATINDEX('%[^0-9]%', @phone) ) > 0
   BEGIN
   --then remove that one character, then continue
   SET @phone = REPLACE(@phone
   , SUBSTRING(@phone, PATINDEX('%[^0-9]%', @phone), 1)
   , '')
   END

SELECT @phone

这篇关于SQL中的PATINDEX不能使用特殊字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！