Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL Syntax

Posted on 2014-02-21
6
Medium Priority
?
444 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
  • 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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
To export Lotus Notes to Outlook PST or Exchange and Domino Server files to Exchange Server or PST files with ease, go for Kernel for Lotus Notes to Outlook conversion tool. Through the video, you can watch the conversion process. A common user with…

601 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