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

x
?
Solved

SQL Syntax

Posted on 2014-02-21
6
Medium Priority
?
426 Views
Last Modified: 2014-02-21
Hey guys,

I have a field in the DB called StoreNum in the table SysInfo. The value of that field maybe from 1 - 9999. I need a query to return that value as a 4 digit string, even if it's not. (ie: leading zeros)

Examples:

If the value is 1, I want the query to return 0001
If the value is 52, I want the query to return 0052
If the value is 961, I want the query to return 0961
If the value is 6547, I want the query to return 6547


SyBase SQL Anywhere v10
0
Comment
Question by:triphen
[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
6 Comments
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 1300 total points
ID: 39877505
I think the sybase syntax is something like (Sorry I don't have the means to test)
select left(replicate("0",4) + convert(varchar,storenum),4)
from sysinfo
0
 

Author Comment

by:triphen
ID: 39877535
Your query came back with Column "0" not found. I tweak to this:

select left(replicate('0',4) + convert(varchar,storenum),4)
from sysinfo

Returns "0000"   :/


select storenum from dba.sysinfo

Returns "1"
0
 
LVL 27

Assisted Solution

by:Shaun Kline
Shaun Kline earned 700 total points
ID: 39877563
Just change LEFT to RIGHT in your revised version of Awking's query.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 32

Accepted Solution

by:
awking00 earned 1300 total points
ID: 39877566
Sorry, should have been -
select right(replicate('0',4) + convert(varchar,storenum),4) from sysinfo
The idea being concatenating the "0000" to the storenum of 1 converted to character to create "00001" then taking the right (not left) four characters of "0001"
0
 
LVL 32

Expert Comment

by:awking00
ID: 39877572
Shaun, you caught me while I was typing :-)
0
 

Author Closing Comment

by:triphen
ID: 39877576
You guys rock! Thank you!!!!
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

618 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