Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extracting Numbers from String


Extracting Numbers from String

Author
Message
petronas40
petronas40
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: 4
I have to extract the record which has 6 numbers which are together from the string .

Customer ID Address
6237 025 OHIO DR APT 13111
9261 123 main street #1567
8036 12 lee street #8956345

I need to choose Customer Id = 8036.

Please help!

Thanks,
Petronas
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2197 Visits: 12568
petronas40 (10/22/2013)
I have to extract the record which has 6 numbers which are together from the string .

Customer ID Address
6237 025 OHIO DR APT 13111
9261 123 main street #1567
8036 12 lee street #8956345

I 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 matching

LIKE [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.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2246 Visits: 7426
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 TABLE
AS
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 Address
6237 025 OHIO DR APT 13111
9261 123 main street #1567
8036 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 Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16669 Visits: 17030
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 #Something
select 6237, '025 OHIO DR APT 13111' union all
select 9261, '123 main street #1567' union all
select 8036, '12 lee street #8956345'

select *
from #Something
where 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 Moden's 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
Michael L John
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2056 Visits: 7407
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 John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2246 Visits: 7426
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 Address
6237 025 OHIO DR APT 13111
9261 123 main street #1567
8036 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 parameter
DECLARE @string varchar(200)=
'Customer ID Address
6237 025 OHIO DR APT 13111
9261 123 main street #1567
8036 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 Address
6237 025 OHIO DR APT 13111
9261 123 main street #1567
8036 12 lee street #8956345'
UNION
SELECT 2,
'Customer ID Address
5537 333 DELEWARE APT 222
6133 888 main street #2
5555 55 hee Ave #44558899'
)
SELECT customer_id, customer_address
FROM customer_import_data cd
CROSS APPLY dbo.string_to_table(cd.cust_data) sp
WHERE customer_address LIKE N'%[0-9][0-9][0-9][0-9][0-9][0-9]%'



-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
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