• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 844
  • Last Modified:

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.
0
jjrr007
Asked:
jjrr007
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

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
 
Vadim RappCommented:
...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
 
jjrr007Author Commented:
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
jjrr007Author Commented:
I'd rather change the data column type, because the query will run faster.  There is a lot of data.  Thanks!
0
 
jjrr007Author Commented:
Also, the columns should only have numbers.  
0
 
Anthony PerkinsCommented:
>>Is their another data type that I could use?<<
bigint
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Anthony PerkinsCommented:
angelIII,

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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now