Convert string to number

[annub]

I am using VB 6.0 with SQL 8.0. I want to convert string to number one of my field in table. I am doing something like this. EMPNo is string in table, i want it to be numeric.

"select jobnumber,substring(empno,3,8)as empno,shiftnumber,line,date " & _
"From empldtllab " & _
"Where Date = '" & FromDate & "'" & _
"and empno like 'PQ%' " & _
"order by date"

 

[bboffin]

Try:

UPDATE empldtllab SET empno = substring(empno,3,8) WHERE empno LIKE 'PQ%'

This will make empno a numeric string wherever it currently starts with PQ.

Assuming that this makes all the empno fields numeric and that they will fit into an int, you can simply change the field type of empno to int using Enterprise Manager.

Gotchas are that if this is the primary key you may end up trying to create duplicates by removing the PQ? bit.

Try this out on a copy of the table before doing it on the live db.

 

[annub]

I can't change EMPNO field as numberic, coz i get row data from scanner with PQ in front in empno fields, that way i know its not empno & its product qty.

 

[bboffin]

If you just want empno to be returned as a number then use the SQL CONVERT function:

CONVERT(int, SUBSTRING(empno, 3, 8)) AS EmpNumber