SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extract only numbers from string which contains special characters too


Extract only numbers from string which contains special characters too

Author
Message
sarithamoolinty
sarithamoolinty
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 43
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.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15359 Visits: 14396
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
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4252 Visits: 7865
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Jeff Moden
    Jeff Moden
    SSC Guru
    SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

    Group: General Forum Members
    Points: 89697 Visits: 41144
    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.
    If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    Orlando Colamatteo
    Orlando Colamatteo
    SSCoach
    SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

    Group: General Forum Members
    Points: 15359 Visits: 14396
    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
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search