Solved

# Using Instring Function?

Posted on 2006-06-14
Medium Priority
845 Views
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.
0
Question by:jjrr007
• 3
• 2
• 2
• +1

LVL 143

Expert Comment

ID: 16903759

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 )

0

LVL 40

Expert Comment

ID: 16903786
...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

0

LVL 1

Author Comment

ID: 16903827
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?
0

LVL 1

Author Comment

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

LVL 1

Author Comment

ID: 16903914
Also, the columns should only have numbers.
0

LVL 75

Expert Comment

ID: 16903977
>>Is their another data type that I could use?<<
bigint
0

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16903992
>it won't accept a value over 4 in length.
wrong. the "size" you see is the number of bytes is uses, int can go up to 2 billion (2'000'000'000)
if that is not enough, use bigint
0

LVL 75

Expert Comment

ID: 16904243
angelIII,

Absolutely.  I overlooked that.  Thanks for pointing that out.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.