[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

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
0
triphen
Asked:
triphen
  • 3
  • 2
3 Solutions
 
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
 
Shaun KlineLead Software EngineerCommented:
Just change LEFT to RIGHT in your revised version of Awking's query.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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:
Shaun, you caught me while I was typing :-)
0
 
triphenAuthor Commented:
You guys rock! Thank you!!!!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now