Query partial match of a numeric value

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

Query partial match of a numeric value

Author

Topic

mefferz
Starting Member

1 Post

Posted - 2010-10-31 : 23:17:20

Hi all,

I'm fairly new to SQL syntax. I administrate a software package with a SQL 2005 backend. I've made a few simple queries for reporting and such, but I'm not quite sure how to perform the following:

I'm trying to query a specific column (account numbers) to return all values that have multiple instances of a base account number. For example, if a value of 54321.1, 54321.2, 54321.3 etc exists. I can't define specific numbers in the query, because I don't know which accounts are going to have multiple instances. I do not want values returned that only have 1 instance. How would I go about attempting this? Thanks.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-01 : 03:52:55

[code]
DECLARE @Table AS TABLE ( AccNo VARCHAR(30) )
INSERT INTO @Table
SELECT '54321.1' UNION ALL
SELECT '54321.2' UNION ALL
SELECT '54321.3' UNION ALL
SELECT '54322.1' UNION ALL
SELECT '54325.1' UNION ALL
SELECT '54321.4'

SELECT BaseAccNo FROM
(
SELECT SUBSTRING(AccNo, 1, CHARINDEX('.', AccNo) - 1) AS BaseAccNo, RIGHT(AccNo, LEN(AccNo) - CHARINDEX('.', AccNo)) Instances, AccNo FROM @Table
) A
GROUP BY BaseAccNo HAVING COUNT(Instances) = 1
[/code]

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-01 : 04:46:55

[code];WITH cteAccountNumbers(AccNo, Items)
AS (
SELECT AccNo,
COUNT(*) OVER (PARTITION BY PARSENAME(AccNo, 2)) AS Items
FROM @Table
)
SELECT AccNo
FROM cteAccountNumbers
WHERE Items > 1[/code]

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-01 : 05:06:46

quote:

Originally posted by Peso

;WITH cteAccountNumbers(AccNo, Items)
AS (
	SELECT	AccNo,
		COUNT(*) OVER (PARTITION BY PARSENAME(AccNo, 2)) AS Items
	FROM	@Table
)
SELECT	AccNo
FROM	cteAccountNumbers
WHERE	Items > 1

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

This will not give required output.
it is also including account numbers having more than one instances.
which is not true for the condition.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-01 : 05:15:17

Really? I read the original condition

quote:
I do not want values returned that only have 1 instance.

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-01 : 06:11:20

quote:
Originally posted by Peso

Really? I read the original condition
quote:
I do not want values returned that only have 1 instance.

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

Ooops...!!!
I apologize
i misunderstood the things.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources