Click here to Skip to main content
15,844,752 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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)
              , '')
Posted
Updated 12-May-16 22:39pm
v2

SQL server 2008 now its work as i write code but when there is single cote in input it fails
SQL
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
 
Share this answer
 
Comments
vishal_h 21-Mar-14 0:40am    
it fails when input is
declare @phone varchar(50)
set @phone='sdf%d'ff$%'
Rocker-Star 16-May-16 17:14pm    
you have to escape single quote (') by adding one more single quote ('') in sql server as it is one of its literals.
try the following
declare @phone varchar(50)
set @phone='sdf%d''ff$%'
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900