?
Solved

Using Instring Function?

Posted on 2006-06-14
8
Medium Priority
?
836 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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 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

by:Anthony Perkins
ID: 16904243
angelIII,

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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

800 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