Please help me

Question

Post reply

Please help me

Amanda Zhang

Newbie

Points: 6
More actions
December 2, 2005 at 5:42 am

#90040

Hi,
I have a urgent query to be done and I cannot figure it out.
The table contains a waccessno column. An example of a waccessno column value is '0000950117-99-000462'. The format of the waccessno column is XXXXXXXXXX-XX-XXXXXX, where X can be a letter or number. I would like a list of all the waccessno values that do meet this format.
Thanks for the help,

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

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Hi - this is not straightforward, so I'm not surprised you are having trouble.

Would it be sufficient to perform the following checks:

1) That the length of the string is 20 and

2) That there are hyphens at positions 11 and 14 and

3 That there are no other hyphens in the string?

This is not so bad ... let me know if that's OK and I'll show you how.

David Burrows SSC Guru Points: 65144 More actions · Answer 2

SELECT waccessno

FROM

WHERE waccessno LIKE '[0-9a-z][0-9a-z][0-9a-z][0-9a-z][0-9a-z][0-9a-z][0-9a-z][0-9a-z][0-9a-z][0-9a-z]-[0-9a-z][0-9a-z]-[0-9a-z][0-9a-z][0-9a-z][0-9a-z][0-9a-z][0-9a-z]'

Far away is close at hand in the images of elsewhere.
Anon.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

Phil Parkin

SSC Guru

Points: 247180

December 2, 2005 at 7:39 am

#607343

Nice one David!

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

Yes David - I agree - in the minimalist trend that I follow though, this too works...

declare @waccessno varchar(20)
set @waccessno = '000095011A-99-000462'
if @waccessno like replicate('[0-9a-z]', 10)
+ '-' 
+ replicate('[0-9a-z]',2) 
+ '-' 
+ replicate('[0-9a-z]',6) 
print 'works'
else
print 'does not work'

**ASCII stupid question, get a stupid ANSI !!!**

David Burrows SSC Guru Points: 65144 More actions · Answer 5

So will

WHERE waccessno LIKE REPLACE('XXXXXXXXXX-XX-XXXXXX','X','[0-9a-z]')

and looks prettier and neater than yours

Far away is close at hand in the images of elsewhere.
Anon.

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

Yes - but you didn't think of the "minimalist" usage at first did you...had to make you put your thinking cap on, didn't I ?!?!...

**ASCII stupid question, get a stupid ANSI !!!**

David Burrows SSC Guru Points: 65144 More actions · Answer 7

Maybe

Besides if I posted it first time you'd have nothing to refer to , then your posts would dry up

Keeps you on your toes eh

Far away is close at hand in the images of elsewhere.
Anon.

sushila SSC-Dedicated Points: 35293 More actions · Answer 8

Don't you worry about my posts "drying up"...I get plenty of fodder from other threads..

As for "keeping me on my toes"...yes indeed - just the way I like it..."on the edge" & "poised for flight"...

**ASCII stupid question, get a stupid ANSI !!!**