How do I: Select only first 5 characters in 0-9 char set? 1

[Qmoto]

FYI: I'm using MS SQL 2000 & I'm a noob... (well at least it feels like it)

Step 1:
How do I select only zip code records that fall within 0-9 character set (there are international zipcodes in the data) and trim the them to 5 characters in length? e.g. turn 12345-1234 into 12345

Step 2:
What's the best way to use the results from step 1 in a where clause on data from another table? (note: the second table has no key values)

psudo code...

select col_1, col_2 from table1
where zipcode = 'results from step 1'

Thanks for the thoughts :0)

 

[Qmoto]

Sorry folks, I just realized how bady I titled this request. It should read more like: How do I select only the first 5 characters from a column if the data is within 0-9.

Oops.

 

[gmmastros]

Select Left(ZipCode, 5)
From TableName
Where IsNumeric(Left(ZipCode, 5)) = 1

-George

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

 

[Qmoto]

Thank you very much sir, I appreciate the quick response!

I have a couple quick questions about this. In the WHERE IsNumeric(Left(ZipCode,5)) = 1 what does the 1 signify? Is this a boolean structure of some kind?

Second question: For future reference, isn't there a way that I verify if the selected character set is within 0-9, a-z, A-Z??


Thanks again for the help!

 

[gmmastros]

IsNumeric returns a 1 when the value it is checking is a valid integer. So the where clause is filtering records where the first 5 characters of the zipcode field is numeric.

-George

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

 

[vongrunt]

Er... thread183-1010578.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Qmoto]

Thanks for the explination & the additional link. Here's a helpful quote of from the link:

ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to at least one of these numeric types.

Thanks again!