select data with only 5 places

[PQTIII]

I want to list only the rtrim[operid] that contain a 5 digit string. I used operid like '_____' and it list all 5 digit operids, but it also list 4 digit ones.

select distinct rtrim(OperId)as OPID , rtrim(OperName) as OP from vWorkers
where OperName is not null and OperId like '____' order by OP

How do I get only fields containing exactly 5 digits to be listed???

Thanks
pt

 

[SQLDenis]

where Len(OperId) = 5

do you also need to check for numeric values only?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

If OperId is a 'string' data type like char, varchar, etc... then use the Len function (Where Len(field) = 5)

If it's an integer column then

Where Field Between 10000 and 99999

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PQTIII]

PERFECT, thank you so much!

pt

 

[DaveJohnson]

Hi

Could anyone tell me where i am going wrong the first part of this works but the second half does nor insert any records.

-- Update where records exist in SY01200
UPDATE SY01200 SET

INET1 = (SELECT EMAIL FROM F100_VM_EMAIL WHERE SY01200.MASTER_ID = F100_VM_EMAIL.ACCOUNT),
INET8 = (SELECT NOTES FROM F100_VM_EMAIL WHERE SY01200.MASTER_ID = F100_VM_EMAIL.ACCOUNT)

FROM SY01200, F100_VM_EMAIL
WHERE SY01200.MASTER_ID IN (SELECT ACCOUNT FROM F100_VM_EMAIL) AND MASTER_TYPE = 'VEN'

-- Insert where no record exists in SY01200

INSERT INTO SY01200 (Master_Type, Master_ID, ADRSCODE, INETINFO, INET1, INET2, INET3, INET4, INET5, INET6, INET7, INET8)

SELECT 'VEN', F100_VM_Email.ACCOUNT, F100_VM_Email.ACCOUNT, ' ', F100_VM_Email.EMAIL, ' ', ' ', ' ', ' ', ' ', ' ', F100_VM_Email.NOTES

FROM SY01200, F100_VM_EMAIL
WHERE F100_VM_EMAIL.ACCOUNT NOT IN (SELECT MASTER_ID FROM SY01200)


Many thanks