## Extracting Numbers from String

 Author Message petronas40 SSC Rookie Group: General Forum Members Points: 39 Visits: 4 I have to extract the record which has 6 numbers which are together from the string . Customer ID Address6237 025 OHIO DR APT 131119261 123 main street #15678036 12 lee street #8956345I need to choose Customer Id = 8036.Please help! Thanks,Petronas pietlinden SSC-Dedicated Group: General Forum Members Points: 32115 Visits: 15179 petronas40 (10/22/2013)I have to extract the record which has 6 numbers which are together from the string . Customer ID Address6237 025 OHIO DR APT 131119261 123 main street #15678036 12 lee street #8956345I need to choose Customer Id = 8036.You could use REPLACE to make all the numbers (somewhat deliberately) run together in the first part of you query and then do pattern matchingLIKE [0-9][0-9][0-9][0-9][0-9][0-9][A-Z]%not totally sure about the last part. (the "not a number") Alan Burstein SSC-Dedicated Group: General Forum Members Points: 32747 Visits: 8581 Edit: misinderstood the requirement. Updated my function accordingly...With the DelimitedSplit8K splitter function you could create an inline Table Valued function like this: `CREATE FUNCTION dbo.string_to_table(@string varchar(8000))RETURNS TABLEAS RETURN( SELECT LEFT(item, CHARINDEX(' ',item)) AS customer_id, RIGHT(item, LEN(item)-CHARINDEX(' ',item)) AS customer_address FROM ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), item FROM dbo.DelimitedSplit8K(@string,CHAR(10))) x(rn, item) WHERE rn>1)GO`Then you could do something like this: `DECLARE @string varchar(200)='Customer ID Address6237 025 OHIO DR APT 131119261 123 main street #15678036 12 lee street #8956345';SELECT * FROM dbo.string_to_table(@string)WHERE customer_address LIKE N'%[0-9][0-9][0-9][0-9][0-9][0-9]%'` -- Alan BursteinHelpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001 Sean Lange SSC Guru Group: General Forum Members Points: 149929 Visits: 18581 This is really pretty straight forward with pattern matching. It is generally considered best practice to post ddl and consumable data when asking for help. I created this for you as an example.`create table #Something( CustomerID int, MyAddress varchar(50))insert #Somethingselect 6237, '025 OHIO DR APT 13111' union allselect 9261, '123 main street #1567' union allselect 8036, '12 lee street #8956345'select *from #Somethingwhere MyAddress LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]%'` _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Modens splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2) Michael L John SSChampion Group: General Forum Members Points: 12417 Visits: 9021 This looks like an import problem! Is the sample data you provided in a database table? If so, how did it get there?It looks like the output into a fixed width text file. First 5 characters are ID, next 40 characters are address, etc. etc.Export it to a text file, and re-do an import. You will have to sift through the file to determine the number of characters in each field, but that will get you columns of what you need.It may make life easier! Michael L JohnIf you assassinate a DBA, would you pull a trigger?To properly post on a forum:http://www.sqlservercentral.com/articles/61537/ Alan Burstein SSC-Dedicated Group: General Forum Members Points: 32747 Visits: 8581 I misunderstood the OP and updated my function to do what I think you are trying to do (it was originally searching for an id instead of six consecutive integers. What I am still not clear about is if this: `Customer ID Address6237 025 OHIO DR APT 131119261 123 main street #15678036 12 lee street #8956345`Represents a table or a string. If it represents a table then Sean understood your requirement correctly and you just need to return records from a column that contain 6 consecutive numbers then what Sean posted is exactly what you need. If Sean was mistaken and you are looking for to extract that information from a string then the function I posted will give you what you want. In my previous post I demonstrated how pull this information from a variable or parameter. Below is how you would do it if the information was in a table...`-- getting this information from a variable or parameterDECLARE @string varchar(200)='Customer ID Address6237 025 OHIO DR APT 131119261 123 main street #15678036 12 lee street #8956345';SELECT * FROM dbo.string_to_table(@string)WHERE customer_address LIKE N'%[0-9][0-9][0-9][0-9][0-9][0-9]%'-- getting this information from a table;WITH customer_import_data(id, cust_data) AS( SELECT 1,'Customer ID Address6237 025 OHIO DR APT 131119261 123 main street #15678036 12 lee street #8956345'UNION SELECT 2,'Customer ID Address5537 333 DELEWARE APT 2226133 888 main street #25555 55 hee Ave #44558899')SELECT customer_id, customer_addressFROM customer_import_data cdCROSS APPLY dbo.string_to_table(cd.cust_data) spWHERE customer_address LIKE N'%[0-9][0-9][0-9][0-9][0-9][0-9]%'` -- Alan BursteinHelpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001