Postcode Query

[slickp007]

Hi, i don't know if i'm posting on the correct forum but this i thought was the most relevant forum.

i'm doing an sql query in our crm package to find clients with particular postcodes. An example of our postcodes are NW12 3RB, N1 2LW, SW12 3BW, etc. I only need the area part, so for the examples i would need NW, N, SW. I created a query below but i'm gettin errors:

SELECT contact1.Accountno, contact1.company, contact1.key4, contact2.uxdate, contact1.contact, contact1.zip
FROM contact1, contact2
WHERE (contact1.accountno=contact2.accountno)
AND (left(contact1.zip,(IF ISNUMERIC(contact1.zip,2) THEN 1 ELSE 2)) in ('NR','N'))
AND (contact1.key4 NOT IN ('EEF','CLA','FPI'))
AND (datepart(mm,contact2.uxdate) IN (08,09))

I get two server messages saying "The isnumeric funtion requires 1 arguments" and "Incorrect syntax near the keyword 'IF'"

Anybody out there that can help, or has a better way of just cutting the area out of the postcode for this query?

 

[gmmastros]

IF ISNUMERIC(contact1.zip,2) THEN 1 ELSE 2)

There are a ton of things wrong with this.

First, you cannot us IF in a query. Instead, you need to use CASE. Like this...

[!]Case[/!] When IsNumeric(....) Then 1 Else 2 [!]End[/!]

Notice that you MUST use END with every Case. That's the structure, and you will get syntax errors otherwise.

Now... for the IsNumeric function. It returns a 1 for numeric and a 0 for non-numeric. So you need to incorporate that in the syntax, like this....

Case When IsNumeric(...) [!]= 1[/!] Then 1 Else 2 End

And finally... it appears as though you want to check the 2nd character to see if it is numeric. To do that, you'll need to use the substring function. Like this...

[tt][blue]Case When IsNumeric([!]SubString([/!]contact1.zip[!], 2, 1)[/!]) [!]= 1[/!] Then 1 Else 2 End[/blue][/tt]

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

>> a better way of just cutting the area out of the postcode for this query?

It appears as though you want to get the characters to the left of any number in the post code. For that, I would use PatIndex. Like this...

Declare @PostCode Table(Data VarChar(20))

Insert Into @PostCode Values('NW12 3RB')
Insert Into @PostCode Values('N1 2LW')
Insert Into @PostCode Values('SW12 3BW')
Insert Into @PostCode Values('SW')

Select Left(Data, PatIndex('%[0-9]%', Data + '0') - 1)
From   @PostCode

This query uses a table variable to store some sample data. You can copy/paste this to a query window to see how it works.

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[slickp007]

Cheers gmmastros. however i now have this:

SELECT contact1.Accountno, contact1.company, contact1.key4, contact2.uxdate, contact1.contact, contact1.zip
FROM contact1, contact2
WHERE (contact1.accountno=contact2.accountno)
AND (contact1.key4 NOT IN ('EEF','CLA','FPI'))
AND ((Case When IsNumeric(SubString(contact1.zip, 2, 1)) = 1 Then 1 Else 2 End) IN ('N','NW'))
AND (datepart(mm,contact2.uxdate) IN (08,09))

is this correct?

i ran this and i get a syntax error "Syntax error converting the varchar value 'NW' to a column of data type int"

help please?

 

[gmmastros]

You removed your LEFT function.

[tt][blue]And Left(contact1.zip, Case When IsNumeric(SubString(contact1.zip, 2, 1)) = 1 Then 1 Else 2 End) IN ('N','NW')[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[slickp007]

sorry i'm being a dumbass, works great, cheers mate!!

 

[kaht]

And, if you wanna slim it down a little bit more you can get rid of the case statement:

And Left(contact1.zip, [!]2 - isNumeric(SubString(contact1.zip, 2, 1))[/!]) IN ('N','NW')

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

Finally, <. is a good thing!

http://www.lessthandot.com