Compare Alpha Numeric Field - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Compare Alpha Numeric Field

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2010-04-23 : 14:43:29

Hello Sir ,

I am unable to compare alpha numeric field like less then and greater than.

Pls help me out sir .

Yaman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 14:45:08

it will only compare based on alphabetic sequence. what exactly do you want to do? plzz explain with data example

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yaman
Posting Yak Master

213 Posts

Posted - 2010-04-23 : 14:50:11

quote:
Originally posted by visakh16

it will only compare based on alphabetic sequence. what exactly do you want to do? plzz explain with data example

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SELECT TLI_PO_NO FROM EMPLYEE WHERE
tli_po_no >='102' AND tli_po_no<=N'984')

Results :-

TLI_PO_NO
-----------
115588
115599
19087
45678
47
966
408
9
970
18
648
802
89
41
14
42
45
78
1478
33
222
964
1-q
28
28-A
45-a
2356
80201
227888
70136
134
984
196
956
960
962
5
333
121213
121212
111222
60242
50133
50134
50135
50136
254
301
302
303
304
306
308
646
647
68
564
58
232
65
20606-A
70123
70128
70134
257
263
645
1-t
85478
23-a
133
80200
80198
80199
80202
80215
80217
80221
111065
111066
111067
70138
70140
70141
22
456
8956
78989
50113
657
151
21
30846-A
30846
30845-A
30845
30847-A
30847-B
30847
30848
156
305
60243
60262
1444
60246
60247
60245
50132
114477
12564-A
111aaa
60252
60230
60231
80194
80206
60251
60244
80203
80210
836442
836486
836515
836540
836549
836829
60249
80204
60250
80205
80207
80208
80209
60253
60256
60254
60255
60257
60258
12648
80196
60241
12649
50125
50126
50127
50128
50129
50130
50131
70137
837363
60248
50137
50138
50139
50140
50141
70139

Yaman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 14:52:47

[code]SELECT TLI_PO_NO FROM EMPLYEE WHERE
ISNUMERIC(tli_po_no)=1
AND tli_po_no*1 >=102 AND tli_po_no*1<=984[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yaman
Posting Yak Master

213 Posts

Posted - 2010-04-23 : 15:49:39

quote:
Originally posted by visakh16

SELECT TLI_PO_NO FROM EMPLYEE WHERE
ISNUMERIC(tli_po_no)=1
AND tli_po_no*1 >=102 AND tli_po_no*1<=984

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I got error :-

" Conversion failed when converting the nvarchar value ' 30542-A' to data type int "

Yaman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 15:51:52

quote:
Originally posted by yaman

quote:
Originally posted by visakh16

SELECT TLI_PO_NO FROM EMPLYEE WHERE
ISNUMERIC(tli_po_no)=1
AND tli_po_no*1 >=102 AND tli_po_no*1<=984

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I got error :-

" Conversion failed when converting the nvarchar value ' 30542-A' to data type int "

Yaman

thats because of your data with nonnumeric part. what do you want to do with them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yaman
Posting Yak Master

213 Posts

Posted - 2010-04-23 : 16:35:12

quote:
Originally posted by visakh16

quote:
Originally posted by yaman

quote:
Originally posted by visakh16

SELECT TLI_PO_NO FROM EMPLYEE WHERE
ISNUMERIC(tli_po_no)=1
AND tli_po_no*1 >=102 AND tli_po_no*1<=984

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I got error :-

" Conversion failed when converting the nvarchar value ' 30542-A' to data type int "

Yaman

thats because of your data with nonnumeric part. what do you want to do with them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I have one column TLI_PO_NO NVARCHAR(50)

How can I compare tli_po_no like < , > and between
It is possible to compare alpha numeric column .

Yaman

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-23 : 18:29:11

quote:
Originally posted by yaman
I have one column TLI_PO_NO NVARCHAR(50)

How can I compare tli_po_no like < , > and between
It is possible to compare alpha numeric column .

Yaman

You have already shown that YES, you can compare alpha numeric values using the <, >, and BETWEEN operators. Additionally, you stated that the results you are getting are not what you expect. You have provided sample data, but not expected results. So, if you want to tell us what results you expect from the sampel data you provided, I bet we can help. Until then, we can only guess at what you actually want.

Maybe this link will help explain:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources