October 22, 2013 at 8:19 pm
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
October 22, 2013 at 8:52 pm
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")
October 23, 2013 at 9:56 am
Edit: misinderstood the requirement. Updated my function accordingly...
With the DelimitedSplit8K[/url] 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
(
SELECTLEFT(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]%'
-- Itzik Ben-Gan 2001
October 23, 2013 at 10:21 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 23, 2013 at 10:39 am
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/
October 23, 2013 at 2:52 pm
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]%'
-- Itzik Ben-Gan 2001
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply