Forums Register Login

SQL string function

+Pie Number of slices to send: Send
Hi All,

How would I do the following in SQL...

My database column has values such as the below
FA-8a0
FA-9a0
FA-10a0

so the format is 2 digit of text then hypen then number then lowercase letter then number

I want to in basic SQL (using substring etc) ensure that where the first number is less then 10 (i.e. 1 to 9) that I
can add a leading zero to it (so the number part is always 2 digits), so it would then convert FA-8a0 to FA-08a0
and FA-9a0 to FA-09a0

FA-10a0 would remain unchanged as the number 10 is 2 digits..

The DB Im using is MS SQL Server 2005.

Any help would be great.

Thanks, Ronan.
+Pie Number of slices to send: Send
 

Ronan Dowd wrote:I want to in basic SQL (using substring etc) ensure that where the first number is less then 10 (i.e. 1 to 9)


FA-10a0 would remain unchanged as the number 10 is 2 digits..



These two statements seems to me saying two things. Are you looking for the first digit after the hypen or the number after the hypen ? I think you need to look at SQL functions or Stored procedures.
+Pie Number of slices to send: Send
Sounds more like something you sort out with a regular expression and the StringBuilder#insert() method.
+Pie Number of slices to send: Send
I would agree with Campbell Ritchie that this is better handled outside of a query ( either redesign the database or use java to manipulate the string)

but since you asked, the code would look something like this: (This is Oracle Syntax and it works. I don't have access to a SQL Server install, so I can't test the syntax there)

if you can always depend on the length of your textCode to be 6 or 7 :


the pipes would obviously be replaced with '+' for SQL Server
It's a beautiful day in this neighborhood - Fred Rogers. Tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 1524 times.
Similar Threads
Binary/Octal/Hex and Decimal Number Systems
parseInt (String s, int radix)
Project Euler Problem 25
how to find digits of the number????
massive non-Mersenne prime
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 23:03:35.