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

Extracting Numbers from String Expand / Collapse
Author
Message
Posted Tuesday, October 22, 2013 8:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 22, 2013 2:26 PM
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
Post #1507447
Posted Tuesday, October 22, 2013 8:52 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 774, Visits: 5,024
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")
Post #1507454
Posted Wednesday, October 23, 2013 9:56 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 581, Visits: 2,712
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1507717
Posted Wednesday, October 23, 2013 10:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
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)
Post #1507730
Posted Wednesday, October 23, 2013 10:39 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 961, Visits: 3,017
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
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Post #1507742
Posted Wednesday, October 23, 2013 2:52 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 581, Visits: 2,712
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1507827
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse