Solved

SQL Syntax

Posted on 2014-02-21
6
399 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 325 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 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 175 total points
ID: 39877563
Just change LEFT to RIGHT in your revised version of Awking's query.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 32

Accepted Solution

by:
awking00 earned 325 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

807 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