how to get the value in between of the postcode ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to get the value in between of the postcode ?

Author

Topic

yeah016
Starting Member

9 Posts

Posted - 2008-08-14 : 02:17:12

hi, can anyone help me ? how to get the value in between of the postcode which in the varchar type?

this is SQL table

[/URL]

when i use the following sql query.

select shipZone from distr_zone_ref where '100000' between Postcode_From AND Postcode_To

i will get the china as the shipzone also...suppose i will get nothing...

and i try to use cast and convert but USA postcode have character.Please help me....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 02:38:13

sorry. i cant view posted image.

yeah016
Starting Member

9 Posts

Posted - 2008-08-14 : 02:43:57

shipzone Postcode_From(varchar) Postcode_To(varchar)
China 10000 14999
USA F 1000 F 1599
UK KA 100 KA 199

here is the table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-14 : 04:32:08

select shipZone from distr_zone_ref where 100000 between Postcode_From*1 AND Postcode_To*1

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-14 : 04:35:27

or

select shipZone from distr_zone_ref where 100000 between Postcode_From*1 AND Postcode_To*1
and Postcode_From not like '%[^0-9]%' and Postcode_To not like '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 04:54:23

100,000 is not between 10,000 and 14,999 anyway.

E 12°55'05.25"
N 56°04'39.16"

yeah016
Starting Member

9 Posts

Posted - 2008-08-15 : 17:27:17

quote:
Originally posted by Peso

100,000 is not between 10,000 and 14,999 anyway.

I know but 100,000 , postcode_from ,postcode_to all is varchar value
so i will get the wrong answer...

yeah016
Starting Member

9 Posts

Posted - 2008-08-15 : 17:30:43

quote:
Originally posted by madhivanan

or

select shipZone from distr_zone_ref where 100000 between Postcode_From*1 AND Postcode_To*1
and Postcode_From not like '%[^0-9]%' and Postcode_To not like '%[^0-9]%'

Thank for reply, this sql query still wrong i get the error message "Conversion failed when converting the varchar value
'F 1000' to data type int."

Any one please, show me the right way

please......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-16 : 01:18:48

Seems like Postcode_From & Postcode_To contains non numeric characters which is causing Postcode_From*1 to break and give this error. Assuming you'll have either numeric data or alphabets followed by numeric (from given sample data formats) use like this

SELECT shipZone FROM distr_zone_ref WHERE 100000 BETWEEN SUBSTRING(Postcode_From,CHARINDEX(' ',Postcode_From)+1,LEN(Postcode_From))
AND 
SUBSTRING(Postcode_To,CHARINDEX(' ',Postcode_To)+1,LEN(Postcode_To))

yeah016
Starting Member

9 Posts

Posted - 2008-08-17 : 11:27:30

thank for reply , but if my postcode didn't contain space then how to do? like F1000 ..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-17 : 12:45:25

SELECT shipZone
FROM distr_zone_ref
WHERE 100000 BETWEEN SUBSTRING(Postcode_From,PATINDEX('%[0-9]%', Postcode_From),LEN(Postcode_From))
AND SUBSTRING(Postcode_To, PATINDEX('%[0-9]%',Postcode_To),LEN(Postcode_To))

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-17 : 13:17:38

quote:
Originally posted by yeah016

thank for reply , but if my postcode didn't contain space then how to do? like F1000 ..

try like this

SELECT shipZone FROM distr_zone_ref WHERE 100000 BETWEEN
REVERSE(LEFT(REVERSE(Postcode_From),PATINDEX('%[A-Z]%',REVERSE(Postcode_From))-1)) AND
REVERSE(LEFT(REVERSE(Postcode_To),PATINDEX('%[A-Z]%',REVERSE(Postcode_To))-1))

yeah016
Starting Member

9 Posts

Posted - 2008-08-20 : 03:43:51

quote:
Originally posted by visakh16

quote:
Originally posted by yeah016

thank for reply , but if my postcode didn't contain space then how to do? like F1000 ..

try like this

SELECT shipZone FROM distr_zone_ref WHERE 100000 BETWEEN
REVERSE(LEFT(REVERSE(Postcode_From),PATINDEX('%[A-Z]%',REVERSE(Postcode_From))-1)) AND
REVERSE(LEFT(REVERSE(Postcode_To),PATINDEX('%[A-Z]%',REVERSE(Postcode_To))-1))

wrong at substring..thx

yeah016
Starting Member

9 Posts

Posted - 2008-08-20 : 03:46:36

quote:
Originally posted by visakh16

quote:
Originally posted by yeah016

thank for reply , but if my postcode didn't contain space then how to do? like F1000 ..

try like this

SELECT shipZone FROM distr_zone_ref WHERE 100000 BETWEEN
REVERSE(LEFT(REVERSE(Postcode_From),PATINDEX('%[A-Z]%',REVERSE(Postcode_From))-1)) AND
REVERSE(LEFT(REVERSE(Postcode_To),PATINDEX('%[A-Z]%',REVERSE(Postcode_To))-1))

thank for reply but this only can get the number type postcode if i want to compare the postcode contain varchar and number this method can't work. Like select shipzone from distr_zone_ref where 'F 1000' between postcode_from and postcode_to

yeah016
Starting Member

9 Posts

Posted - 2008-08-20 : 03:50:58

select shipping_zone from distr_zone_ref where '100000' between postcode_from and postcode_to and len('100000') = len(postcode_from) or len('100000') = len(postcode_to)

but for this only can use if the postcode_ from and postcode_to both length is the same. This is what I can think. Anyone have more good query? thank very much for this forum ^^ and those who are reply me really thanks..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources