retrieve numerice value from nvarchar

mateenmohd · July 18th, 2003, 03:04 AM

Hi,

How can retrieve numeric value from the nvarchar data type.

contractno. (field nvarchar)
-------------------------
000
001
00a
050
02d
78b
090c
305d
659f

and so on....

select * from contract where contractno between '01' and '100'

above query retrieve three digit records between 01 to 100
I want to retrieve record between 1 to 100

if I want to retrieve records between a to z what query will use ?

what query will use in above two condition, please help.
I am using sql server with asp.

Mateen
[email protected]

nbryson · July 18th, 2003, 04:06 AM

Hi

select * from contract where contractno like '%[a-z]%'
will give you the values containing a to z

Could you give more detail on the 1st point.

e.g. do you want only values with numbers ranging from 1 - 100 and not 001 for example.

Regards

Nickie

mateenmohd · July 18th, 2003, 11:01 AM

thanks for youre response.
I want to retrieve records ranging from 001 - 100 .
because records are save 001, 009, ......

retrieve actuall range records not retrieve out of
range records.

Mateen

Quote:

quote:Originally posted by nbryson
Hi

select * from contract where contractno like '%[a-z]%'
will give you the values containing a to z

Could you give more detail on the 1st point.

e.g. do you want only values with numbers ranging from 1 - 100 and not 001 for example.

Regards

Nickie

David Cameron · July 20th, 2003, 06:30 PM

It appears that if there are characters in the column, then they are always at the end of the string. Is this the case?

regards
David Cameron

mateenmohd · July 20th, 2003, 11:25 PM

yes

Quote:

quote:Originally posted by David Cameron
It appears that if there are characters in the column, then they are always at the end of the string. Is this the case?

regards
David Cameron

David Cameron · July 20th, 2003, 11:31 PM

This may be a little slow due to the LIKE, but it should be OK.

Code:

UPDATE MyTable
SET MyCol = LEFT(MyCol, LEN(MyCol) - 1)
WHERE RIGHT(MyCol, 1) LIKE '[^0-9]'

regards
David Cameron

mateenmohd · July 21st, 2003, 03:52 AM

thanks for help

Quote:

quote:Originally posted by David Cameron
This may be a little slow due to the LIKE, but it should be OK.

Code:

UPDATE MyTable
SET MyCol = LEFT(MyCol, LEN(MyCol) - 1)
WHERE RIGHT(MyCol, 1) LIKE '[^0-9]'

regards
David Cameron