SQL Syntax

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
triphenAsked:
Who is Participating?
 
awking00Commented:
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
 
awking00Commented:
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
 
triphenAuthor Commented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Shaun KlineLead Software EngineerCommented:
Just change LEFT to RIGHT in your revised version of Awking's query.
0
 
awking00Commented:
Shaun, you caught me while I was typing :-)
0
 
triphenAuthor Commented:
You guys rock! Thank you!!!!
0
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.

All Courses

From novice to tech pro — start learning today.