Click here to Skip to main content
12,951,034 members (59,597 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hello Experts

Some users had inserts something other than numbers in the 'Tel' column.
Now I want to remove them.

There is a query to find these kind of records:
Select    Tel
          From    Person
          Where   Tel	Like	N'%[أ-ي]%'  And
                  Tel   Is Not  Null

Results:
    Tel
--------------
داخلي 298
داخلي 111
مغازه 2701646
2802301 منزل

How I can Remove the extra info in a sql query?
Posted 24-Nov-13 23:58pm
Comments
Amir Mahfoozi 25-Nov-13 7:01am
   
Replace "Select Tel" with "Delete" and all of them will go.
Meysam Tolouee 25-Nov-13 7:10am
   
You didn't get my question.
See the accepted answer.
Thank you anyway.
Amir Mahfoozi 25-Nov-13 7:33am
   
Ok I got it.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Hi,

Try below sample TSQL to remove non numeric characters from any string/varchar.
To use in sql query you need to create function and then you can use in any sql query also.

DECLARE @cString    VARCHAR(32)
DECLARE @nPos    INTEGER
SELECT  @cString = '90$%45623 *6%}~:@'
SELECT  @nPos = PATINDEX('%[^0-9]%', @cString)
 
WHILE @nPos > 0
BEGIN
SELECT @cString = STUFF(@cString, @nPos, 1, '')
SELECT  @nPos = PATINDEX('%[^0-9]%', @cString)
END
 
SELECT @cString
  Permalink  
Comments
Meysam Tolouee 25-Nov-13 8:21am
   
Thank you for your answer. What if I want to remove only the alphabets?
maulikdusara 25-Nov-13 9:08am
   
use PATINDEX('%[a-z]%', @cString) instead of PATINDEX('%[^0-9]%', @cString)

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
OriginalGriff 5,984
CHill60 3,460
Maciej Los 3,053
Jochen Arndt 1,975
ppolymorphe 1,820


Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 25 Nov 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100