SQL Command in Crystal Reports

I need to write an sql command to use in a crystal report. In that command one of the fields I need is soc# formated as character with the separating hyphens. The incoming field is a 9 position numeric. I need to do this so I can link to other tables that have the soc# formated as character with the separators. I am having a problem finding what I need to change it to character, so I can substring and concatenate to do the reformat.
qbjgqbjgConsultantAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
jgbreedenConnect With a Mentor Commented:
You could just pad some leading zeros then take the RIGHT 9 chars:
RIGHT("000"+CONVERT(CHAR(9),"pr500ap"."prssno"), 9)
0
 
jgbreedenCommented:
would that be CONVERT(CHAR(9), fieldname)
0
 
qbjgqbjgConsultantAuthor Commented:
This is the code I was trying, that it did not like:
 substring("pr500ap"."prssno",1,3) + '-' +
          substring("pr500ap"."prssno",4,2) + '-' +
          substring("pr500ap"."prssno",6,4)
So how can I use the CONVERT(CHAR(9) in this statement?
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!

 
jgbreedenCommented:
if that is a numeric field, you convert it before you apply any string functions:
substring(CONVERT(CHAR(9),"pr500ap"."prssno"),1,3)
0
 
jgbreedenCommented:
another thought, if that is a numeric field, it will strip off any leading zeros, you will need to check the length and pad it with zeros if it is less than 9 digits.
0
 
qbjgqbjgConsultantAuthor Commented:
How do I check the number of digits?
0
All Courses

From novice to tech pro — start learning today.