Convert character to number

[krizzie khonnie]

Hi,

Please help:
[code=mysql]
select *
from v_diff_id_group _type
where id_group = '001' –> color_id from form (char)
[/code]

How can I convert the said query without changing the data type of the ID_GROUP Field (Varhar2).

If a user enters number 1 (instead of 001 for black color), without modifying the above function, it will return multiple records as 1 could mean 001, 00001, 1, etc. So it will read 001, 1, 00001, etc as numbers?


My objective is to keep it as char type then make the query capable of converting it as numeric (opposite of to_char?).

[Atli]

Hi.

Is this a MySQL query? There is no Varchar2 datatype in MySQL.

One thing I don't get. You have a string as an ID, but you wan't the user to be able to enter that ID as a number?

If you use '001' and '0001' as ID's for two separate rows, and the user simply types 1, then how do you know he want's '001', but not '0001'? (or just '1', for that matter).

If you really want to 'hard-code' that into your query, you could always use CONCAT. Like:

SELECT CONCAT('00', 1);

[krizzie khonnie]

hi,

Is varchar2 same with character? Anyway, I already figured out the query:

  select description
     from v_diff_id_group_type
  where to_number(id_group) = I_id  --> 1, 001, 00001 --> returned result is black
    and diff_type = 'C'
    and id_group_ind = 'ID';

Though I'm not sure if I did the right way (since I just experimented that query :)) but the returned result is what I expected.Everyt ime I enter the numbers - 1, 01, 001, 00001 - all results were the same.
We are not allowed to hard code since I just place an example there.
Thanks... :)

[Atli]

Like I say. There is no Varchar2 data type in MySQL. I'm guessing this is a MSSQL thing.

Glad you found a solution tho.

P.S.
Please use [code] tags when posting your code examples.