Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extract only numbers from string which contains special characters too Expand / Collapse
Author
Message
Posted Friday, January 11, 2013 12:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 31, 2013 12:37 AM
Points: 1, Visits: 36
Hi,
i have a column called phone numbers whose datatype is varchar(50) that means it contains integer values, string values, special characters and combination of these three. My requirement is i want to extract only first 10 integer values excluding characters and special characters.

please help me.

Thanks in advance.
Post #1405792
Posted Sunday, January 13, 2013 9:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:19 PM
Points: 7,127, Visits: 12,655
My solution requires the use of a Tally Table (a.k.a. a Numbers Table). For this example my tally table is stored in the master database and is made accessible by everyone who can log into the instance. Feel free to park your tally table in a user database (e.g. a utility-type database) if you do not like it existing in master.

You can learn about tally tables from one of the community's esteemed members, Jeff Moden:

The "Numbers" or "Tally" Table: What it is and how it replaces a loop. By Jeff Moden, 2008/05/07

SELECT  id, phone_number, 
MAX(CASE WHEN position = 1 THEN good_char ELSE '' END) +
MAX(CASE WHEN position = 2 THEN good_char ELSE '' END) +
MAX(CASE WHEN position = 3 THEN good_char ELSE '' END) +
MAX(CASE WHEN position = 4 THEN good_char ELSE '' END) +
MAX(CASE WHEN position = 5 THEN good_char ELSE '' END) +
MAX(CASE WHEN position = 6 THEN good_char ELSE '' END) +
MAX(CASE WHEN position = 7 THEN good_char ELSE '' END) +
MAX(CASE WHEN position = 8 THEN good_char ELSE '' END) +
MAX(CASE WHEN position = 9 THEN good_char ELSE '' END) +
MAX(CASE WHEN position = 10 THEN good_char ELSE '' END) AS cleansed_phone_numbers
FROM dbo.phone
CROSS APPLY (
SELECT SUBSTRING(phone_number, n, 1),
RANK() OVER (ORDER BY n)
FROM master.dbo.tally
WHERE n <= LEN(phone_number)
AND SUBSTRING(phone_number, n, 1) LIKE '[0-9]'
) x (good_char, position)
GROUP BY id, phone_number
ORDER BY id, phone_number;


edit: fix tabbing in code


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1406470
Posted Sunday, January 13, 2013 6:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:49 PM
Points: 1,787, Visits: 5,722
And another Tally type version, but seems faster to me...


CREATE FUNCTION dbo.fn_extract_chars_in_range(
@input varchar(8000) -- the string to be cleansed
,@start char(1) -- the first valid character
,@end char(1) -- the last valid character
,@maxlength smallint=10 -- maximum length to be returned
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
-- dynamic tally table
WITH
c10(c) AS(SELECT '' FROM (VALUES(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),('')) as c20(c))
,c100(c) AS(SELECT '' FROM c10 a,c10 b)
,c10000(c) AS(SELECT '' FROM c100 a,c100 b)
-- the final select numbers the rows. the TOP is important for speed.
,Tally(N) AS(SELECT TOP(LEN(@input)) row_number() OVER(ORDER BY (SELECT NULL)) FROM c10000)

SELECT
CAST(
(
-- extract valid characters and concatenate them
SELECT TOP(@maxlength) substring(@input,N,1)
FROM Tally
WHERE substring(@input,N,1) BETWEEN @start AND @end
FOR XML PATH('')
) AS VARCHAR(8000)
) AS result;

SELECT result
FROM SomeTable
CROSS APPLY dbo.fn_extract_chars_in_range([phone numbers],'0','9',10)



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1406537
    Posted Sunday, January 13, 2013 8:43 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Yesterday @ 11:19 PM
    Points: 37,075, Visits: 31,636
    mister.magoo (1/13/2013)
    And another Tally type version, but seems faster to me...

    It's also good for more than just 10 characters, as well.


    --Jeff Moden
    "RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1406553
    Posted Sunday, January 13, 2013 9:21 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: 2 days ago @ 7:19 PM
    Points: 7,127, Visits: 12,655
    mister.magoo (1/13/2013)
    And another Tally type version, but seems faster to me...

    That's solid. I meant to come back to this because using RANK seemed clunky and I knew there had to a better way. Thanks for refreshing my memory on the XML PATH technique to pivot the good chars back into a string without a delimiter. The method you showed is clearly a better solution than mine but it was a fun exercise nonetheless.


    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
    Post #1406559
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse