Select Numbers with pattern like AAAA.xx

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

SQL Server 2005 Forums

Transact-SQL (2005)

Select Numbers with pattern like AAAA.xx

Author

Topic

doanminhchau
Starting Member

9 Posts

Posted - 2012-03-18 : 22:07:47

Hi, everyone,
I tried to select all Number from Numbers column with the six last numbers must follow the AAAA.xx pattern.
I wrote this query, but I found that if six last numbers have a blank or special character, the query cannot be right. My query is as below:


SELECT Number in Numbers where substring(right(Number,6),6,1)=substring(right(Number,6),5,1) and substring(right(Number,6),3,1)=substring(right(Number,6),4,1) and substring(right(Number,6),3,1)=substring(right(Number,6),2,1) and substring(right(Number,6),2,1)=substring(right(Number,6),1,1)

Please help me, any one could give me some advice.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-19 : 00:26:33

[code]
SELECT Number
from Numbers
where PARSENAME(Number,1) = PARSENAME(RTRIM(REVERSE(Number)),2)
AND PARSENAME(Number,2) = PARSENAME(RTRIM(REVERSE(Number)),1)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

doanminhchau
Starting Member

9 Posts

Posted - 2012-03-19 : 01:47:30

Thanks for your help visakh16, but I don't understand your answer, I read about the PARSENAME function in msdn, this function only returns parts of an object that can be retrieved are the object name, owner name, database name, and server name.
could you help me clarify your answer?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-19 : 09:39:14

first try and see if it fits your requirement.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

doanminhchau
Starting Member

9 Posts

Posted - 2012-03-19 : 21:15:05

Sorry visakh16, it doesn't fit my requirement, I checked your query cannot find the number I want, in my database, I added some numbers such as 01234 9999 00, 01456 0000 88, it cannot find.
Please you help me check.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-20 : 10:43:49

SELECT * FROM dbo.Table1 WHERE Col1 LIKE '%[0-9][0-9][0-9][0-9] [0-9][0-9]'

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-20 : 10:48:04

[code]SELECT Col1
FROM (
SELECT Col1,
RIGHT(REPLACE(Col1, ' ', ''), 6) AS tmp
FROM dbo.Table1
) AS d
WHERE SUBSTRING(tmp, 1, 1) = SUBSTRING(tmp, 2, 1)
AND SUBSTRING(tmp, 2, 1) = SUBSTRING(tmp, 3, 1)
AND SUBSTRING(tmp, 3, 1) = SUBSTRING(tmp, 4, 1)
AND SUBSTRING(tmp, 5, 1) = SUBSTRING(tmp, 6, 1)[/code]

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:02:51

quote:
Originally posted by doanminhchau

Sorry visakh16, it doesn't fit my requirement, I checked your query cannot find the number I want, in my database, I added some numbers such as 01234 9999 00, 01456 0000 88, it cannot find.
Please you help me check.
Thanks

how will above numbers come under AAAA.xx pattern?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

doanminhchau
Starting Member

9 Posts

Posted - 2012-03-20 : 21:29:03

Thanks visakh16 for your help, as my above requirement, the pattern is applied to six last numbers.
Thanks SwePeso for your answer, I found my idea from your help and I got a solution for my requirement as below:

SELECT n.Number FROM Numbers AS n where substring(RIGHT(REPLACE(replace(Number, ' ', ''),'.',''), 6),6,1)=substring(RIGHT(REPLACE(replace(Number, ' ', ''),'.',''), 6),5,1) and substring(right(REPLACE(replace(Number, ' ', ''),'.',''), 6),3,1)=substring(RIGHT(REPLACE(replace(Number, ' ', ''),'.',''), 6),4,1) and substring(RIGHT(REPLACE(replace(Number, ' ', ''),'.',''), 6),3,1)=substring(RIGHT(REPLACE(replace(Number, ' ', ''),'.',''), 6),2,1) and substring(RIGHT(REPLACE(replace(Number, ' ', ''),'.',''), 6),2,1)=substring(RIGHT(REPLACE(replace(Number, ' ', ''),'.',''), 6),1,1)

Because my string contains two special characters such as: blank and dot. Any one can give me better idea help my query shorter
Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-21 : 04:19:52

[code]SELECT Col1
FROM (
SELECT Col1,
RIGHT(REPLACE(REPLACE(Col1, ' ', ''), '.', ''), 6) AS tmp
FROM dbo.Table1
) AS d
WHERE SUBSTRING(tmp, 1, 1) = SUBSTRING(tmp, 2, 1)
AND SUBSTRING(tmp, 2, 1) = SUBSTRING(tmp, 3, 1)
AND SUBSTRING(tmp, 3, 1) = SUBSTRING(tmp, 4, 1)
AND SUBSTRING(tmp, 5, 1) = SUBSTRING(tmp, 6, 1)[/code]

N 56°04'39.26"
E 12°55'05.63"

doanminhchau
Starting Member

9 Posts

Posted - 2012-03-21 : 04:59:18

Thanks SwePeso,
Good job, your query is shorter and clearer than mine.
Thanks again.
Problem is solved.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-21 : 06:30:19

Your welcome.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-21 : 06:32:56

You can also shorten down the code to this

SELECT	Col1
FROM	(
		SELECT	Col1,
			RIGHT(REPLACE(REPLACE(Col1, ' ', ''), '.', ''), 6) AS tmp
		FROM	dbo.Table1
	) AS d
WHERE	SUBSTRING(tmp, 1, 3) = SUBSTRING(tmp, 2, 3)
	AND SUBSTRING(tmp, 5, 1) = SUBSTRING(tmp, 6, 1)

N 56°04'39.26"
E 12°55'05.63"

doanminhchau
Starting Member

9 Posts

Posted - 2012-03-21 : 22:42:39

Thanks for your enthusiasm, SwePeso, your query is so great.
Nice to meet you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources