Solved

Using Instring Function?

Posted on 2006-06-14
8
814 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 142

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now