Solved

Using Instring Function?

Posted on 2006-06-14
8
832 Views
Last Modified: 2010-08-05
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
Comment
Question by:jjrr007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
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

by:Vadim Rapp
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

by:jjrr007
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 1

Author Comment

by:jjrr007
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

by:jjrr007
ID: 16903914
Also, the columns should only have numbers.  
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16903977
>>Is their another data type that I could use?<<
bigint
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

by:Anthony Perkins
ID: 16904243
angelIII,

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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question