Getting Consecutive numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting Consecutive numbers

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2013-10-22 : 10:36:10

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-22 : 11:47:29

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?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-22 : 11:51:40

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

Petronas
Posting Yak Master

134 Posts

Posted - 2013-10-22 : 11:54:38

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

Petronas
Posting Yak Master

134 Posts

Posted - 2013-10-22 : 12:06:59

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.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-22 : 12:12:35

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

Petronas
Posting Yak Master

134 Posts

Posted - 2013-10-22 : 12:17:59

TJ,

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

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-22 : 13:36:07

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

Petronas
Posting Yak Master

134 Posts

Posted - 2013-10-22 : 15:41:48

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-22 : 21:40:27

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]%'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-22 : 22:16:48

shoot - I over thought. consecutive vs. sequential!

pretty cool sequential solution though

Be One with the Optimizer
TG

Petronas
Posting Yak Master

134 Posts

Posted - 2013-10-23 : 09:23:29

Thank you Waterduck and TJ for taking the time to help me with it. I appreciate it . Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources