Extract only numbers from string which contains special characters too

• sarithamoolinty

  Grasshopper

  Points: 21

  More actions

  January 11, 2013 at 12:56 am

  #144061

  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

  SSC Guru

  Points: 182276

  More actions

  January 13, 2013 at 9:08 am

  #1576680

  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[/url]

  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

  SSC-Forever

  Points: 47068

  More actions

  January 13, 2013 at 6:22 pm

  #1576736

  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
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]