Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL query help with error

Posted on 2014-02-21
6
Medium Priority
?
178 Views
Last Modified: 2014-02-21
Ok I am investigating a query that someone developed for my company:

CAST(CAST(SUBSTRING(KeyField, 7, 10) AS BIGINT) AS varchar) AS OrderLineLink

The keyfield column has data such as....
0000230008    retult....    8
0000290004    result....   4

but now im getting data in the Keyfield such as...

000023AER8

This is obv causing a crash. If the conversion is not possible how can I get it to use null?
0
Comment
Question by:deanlee17
[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
  • 3
6 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39876329
try only

SUBSTRING(KeyField, 10, 10) AS OrderLineLink
0
 

Author Comment

by:deanlee17
ID: 39876338
Can you explain how its working?
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39876348
SUBSTRING(KeyField, 10, 10)

this will give you last charater in the given string

your string have length 10 , this formula provide last charater of it
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:deanlee17
ID: 39876364
Actually, sorry to change the question slightly, but ive just noticed it needs to start far right and move left until it hits aa zero, so....

0000290004  = 4
0000290014  = 14
0000290144  = 144

Thank you.
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 39876432
try this

charIndex( '0',Reverse(KeyField)),Substring (KeyField ,Len(KeyField)- charIndex( '0',Reverse(KeyField))+2,Len(KeyField))
0
 

Author Comment

by:deanlee17
ID: 39876447
Thank you
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Screencast - Getting to Know the Pipeline

610 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