data convirsion

[shivkumar]

I have a table prepared in SQL server. there are multiple columns in the table, voucher_no is one of them, and its datatype is 'varchar'.
When I am executing the following query
'Select distinct voucher_no from mytable order by voucher_no'

it is not generating the correct result. because voucher_no is not a numeric field.


Can anybody tell me how can I convert this varchar data into numeric values using select statement, so that it can produce proper result.

I am waiting


with best regards
shiv

[exterminator]

it is not generating the correct result. because voucher_no is not a numeric field.ORDER BY works on strings. What is your expected result? Note that it would be ordering by comparing the ascii values of the characters. Also, ensure that you have an index on the column to enhace the ordering. Regards.

[shivkumar]

I executed the following query
select distinct (voucher_no) from journal_vouchers where (tran_code='J01' or tran_code='J51') order by voucher_no

and I found the following result
1
10
100
1000
1001
1005
1006
1009
101
1010
1011
1015
1016
1017
103
1030
1031
1032


while I found the result in the following fashion
1
10
100
101
103
1000
1001
1005
1006
1009
1010
1011
1030
---------
--------

keep in mind that voucher_no is varchar




with regards

[exterminator]

Why do you make the column as varchar when it is only keeping numeric values? Is it supposed to keep alphanumeric values? If that is so.. the current thing is ok and even the order by gives you the correct thing...

However.. you could use a cast/convert function on the column with the order by clause... cast/convert (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp). But again if you have any of the records having a non-null non-numeric values.. I suppose the whole thing should fail.

This solution is way poor and would reduce the efficiency of the order by even if that particular column is indexed. Can you not change the column to a numeric one? If you can, I do have a solution for that. Hope this helps.

[shivkumar]

thanks for your kind suggestion.
I want to know that whether cost/convert will affect the table. If it may affect then please suggest any other solution. Because the database is having lacks of records.
If it would be a numeric type then I had no problem. since it is a alphanumeric, and I have to do it any way. So if you have any possible solution, I will be happy to have that.


regards
shiv

[exterminator]

No it will not affect the table structure.. you could have had checked this so easily but just making a small test table and putting some data... it was not tough. What difference would casting make if it really affected your record type? It was in no way going to do an alter table.

Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.
Hope this helps. Regards.

[shivkumar]

thanks,
the following query converted the column but it did not sort the column

select distinct cast (voucher_no as numeric(15))vouchers from results

and when I used the following query
"select distinct cast (voucher_no as numeric(15))vouchers from results order by voucher_no"

it generated an error saying
"Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified."



how can I produce the sorted result.

regds
shiv

[exterminator]

You applied it at the wrong place.. apply it on the column_name in the order by clause and not on the column_name in the select clause.. Hope this helps. Regards.

[shivkumar]

thanks dear friend,
it solved my problem.

[exterminator]

thanks dear friend,
it solved my problem.You are welcome :) .. glad that I helped. Regards.