asked on

Using Instring Function?

Experts,

I have a column that is prefilled with zeros. The column has a length of 15. What I need to do is join it to another column that isn't prefilled with zeros. My thought is that I need to change the column that is prefilled with zeros.

How do I do this? Please provide the code. Thanks a lot!

***Ignore this part below, unless you need a method to do this***
It may work like this,
Instring for first non-zero character from the left side. Then take this result and subtract it from 15. Then use this result in a right function to determine how many charachters from the right side to use.

Guy Hengel [angelIII / a3]

select ...
from table1
join table2
on table1.integer_field = cast( table2.varchar_field as integer )

if both are of type varchar:

select ...
from table1
join table2
on cast ( table1.varchar_field as integer ) = cast( table2.varchar_field as integer )

Vadim Rapp

...INNER JOIN mytable
ON
len(non-prefilled-column)=14 and prefilled column = '0' + not-prefilled-column
or
len(non-prefilled-column)=13 and prefilled column = '00' + not-prefilled-column
or
...
or
len(non-prefilled-column)=1 and prefilled column = '00000000000000' + not-prefilled-column

jjrr007

ASKER

They are both nvarchar.

I would prefer to change the whole column data type. Maybe I could change the column data type to integer. The only issue with changing the column type to integer, it won't accept a value over 4 in length.

Is their another data type that I could use?

jjrr007

ASKER

I'd rather change the data column type, because the query will run faster. There is a lot of data. Thanks!

jjrr007

ASKER

Also, the columns should only have numbers.