SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Getting Consecutive numbers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Petronas
Posting Yak Master

133 Posts

Posted - 10/22/2013 :  10:36:10  Show Profile  Reply with Quote
Hi,

I have a table with the following :
Cust id Address
1234 25 main street apt 78961
5678 25 ohio dr apt 567891
7890 25 lee lane apt 6789

I want to pull only the 6 consecutive numbers from my address , so only cust id 5678 should be displayed.

Thanks for your help in advance,
Petronas

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/22/2013 :  11:47:29  Show Profile  Reply with Quote
quote:
Originally posted by Petronas

Hi,

I have a table with the following :
Cust id Address
1234 25 main street apt 78961
5678 25 ohio dr apt 567891
7890 25 lee lane apt 6789

I want to pull only the 6 consecutive numbers from my address , so only cust id 5678 should be displayed.

Thanks for your help in advance,
Petronas

I must admit, I am not following the rule that you used to show only custid = 5678. There are no six consecutive numbers in the address for row 2.

What was the rule you used to eliminate 789 from the first row and 6789 from the third row?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5914 Posts

Posted - 10/22/2013 :  11:51:40  Show Profile  Reply with Quote
So your [address] column has complete addresses like <streetNumber> <Street> <suite> <city> <state> <zip> etc. And you want to select rows any part of that value is 6 consecutive digits?

is this the value you say is consecutive? "567891"

Is this consecutive too? "567890"



Be One with the Optimizer
TG
Go to Top of Page

Petronas
Posting Yak Master

133 Posts

Posted - 10/22/2013 :  11:54:38  Show Profile  Reply with Quote
I am sorry James for not explaining properly. I want to extract any numbers that are more than 6 which are together from the address string.
Thanks for responding,
Petronas
Go to Top of Page

Petronas
Posting Yak Master

133 Posts

Posted - 10/22/2013 :  12:06:59  Show Profile  Reply with Quote
Thanks for looking TG. My customer table has Address 1, address 2, city,state,zip. I want to pull all the records that have 6 numbers together from the address 1. In the above example it should pull only Cust id 5678. I appreciate your help.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5914 Posts

Posted - 10/22/2013 :  12:12:35  Show Profile  Reply with Quote
could you please answer the last two questions I posted?
And another one:
Can the numbers appear anywhere in the value or always at the end?

Be One with the Optimizer
TG
Go to Top of Page

Petronas
Posting Yak Master

133 Posts

Posted - 10/22/2013 :  12:17:59  Show Profile  Reply with Quote
TJ,

Yes, to both of your last questions. The numbers can appear anywhere in the string.

Thanks!
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5914 Posts

Posted - 10/22/2013 :  13:36:07  Show Profile  Reply with Quote
quote:

is this the value you say is consecutive? "567891"
Is this consecutive too? "567890"


They both can't be "Yes"

Here's one way. It won't be fast on a big table.
I changed your value to "25 ohio dr apt 567890" so it would be consecutive. if you want to define a different definition of consecutive then just change the string in the substring function.


;with yourTable (Custid, Address)
as
(
       select 1234, '25 main street apt 78961' union all
       select 5678, '25 ohio dr apt 567890' union all
       select 7890, '25 lee lane apt 6789'
)

select t.*
from   yourTable t
inner join master.dbo.spt_values n
       on n.type = 'P'
       and n.number < 10
       and n.number > 0
where  patindex('%' + substring('012345678901234', n.number, 6) + '%', t.address) > 0


OUTPUT:
Custid      Address
----------- ------------------------
5678        25 ohio dr apt 567890


Be One with the Optimizer
TG
Go to Top of Page

Petronas
Posting Yak Master

133 Posts

Posted - 10/22/2013 :  15:41:48  Show Profile  Reply with Quote
Thanks TG. I very much appreciate your help with this. It is working great for consecutive numbers. But if I have to pull any number which is greater than 6 which is not necessarily a consecutive number what do I replace the string with?

Thanks again,
Petronas
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
939 Posts

Posted - 10/22/2013 :  21:40:27  Show Profile  Reply with Quote
i thought you can write it in

;with yourTable (Custid, Address)
as
(
       select 1234, '25 main street apt 78961' union all
       select 5678, '25 ohio dr apt 567890' union all
       select 7890, '25 lee lane apt 6789'
)
SELECT	*
FROM	yourTable
WHERE Address LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]%'
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5914 Posts

Posted - 10/22/2013 :  22:16:48  Show Profile  Reply with Quote
shoot - I over thought. consecutive vs. sequential!

pretty cool sequential solution though

Be One with the Optimizer
TG
Go to Top of Page

Petronas
Posting Yak Master

133 Posts

Posted - 10/23/2013 :  09:23:29  Show Profile  Reply with Quote
Thank you Waterduck and TJ for taking the time to help me with it. I appreciate it . Thanks again!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000