Query help again

Question

Post reply

Query help again

Charlie-120533

SSCrazy

Points: 2187
More actions
April 1, 2008 at 10:23 am

#71296

Good morning again gurus
I have another question regarding the select query. I have a Person table with badgenumber, employeenumber
lastname, initials. The employeenumber column has some bad data in it. The entries run from one character
to alphanumeric to social security numbers with hyphens! I need to run a query that will select only data
that is not six characters (digits) long. The six digits are in a range of 100000 to 103999. What I am
using right now is:
SELECT distinct badgenumber, employeenumber, lastname, initials
FROM PersonTable
where employeenumber '103999' or employeenumber is null
order by lastname, initials
What I want to try is something like:
SELECT badgenumber, employeenumber, lastname, initials
FROM Persontable
WHERE employeenumber is not 6 characters long
order by lastname
Obviously this does not work. Please offer a quick point in the right direction.
Thanks,

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

WHERE LEN(EmployeeNumber) <> 6

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Charlie-120533 SSCrazy Points: 2187 More actions · Answer 2

Somehow part of my post was lost. The query I am using is:

SELECT distinct badgenumber, employeenumber, lastname, initials

FROM PersonTable

where employeenumber '103999' or employeenumber is null

order by lastname, initials

Thanks John. I need to have the length in the range of 100000 to 103999.

I'll try your suggestion and see. It just might work. I'll let you know.

John Rowan SSC Guru Points: 56440 More actions · Answer 3

You'll have to play around with this, but PATINDEX is probably the way to go here. I tried to do this using a derived table, but had no luck. Here's a workable solution to finding the values you need but you'll have to play with it to fit your problem and scope.

DECLARE @Table TABLE (Col1 varchar(15))

INSERT INTO @Table

SELECT '505-78-6101' UNION ALL

SELECT 'atest' UNION ALL

SELECT '101000' UNION ALL

SELECT '102555' UNION ALL

SELECT '104000' UNION ALL

SELECT 'six 66'

DECLARE @tempTable TABLE (Col1 varchar(15), intCol1 int)

INSERT INTO @tempTable

SELECT Col1, CAST(Col1 as int) as intCol1

FROM @Table

WHERE PATINDEX('%[^0-9]%', ISNULL(Col1, '*')) = 0

SELECT *

FROM @tempTable

WHERE intCol1 BETWEEN 100000 AND 103999

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Charlie-120533 SSCrazy Points: 2187 More actions · Answer 4

Thanks again, John. Your first WHERE statement is enough for me to get what I needed. There were only about 2500 rows so not too much to go through. I knew it was simple ( the first one). I still need to work on my TSQL in order to understand your second response. Just need to find the time and I will dissect it to fully understand what is going on. Then, I'll need to start writing them myself!!

Again, Thanks.

😛