wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQL string function Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL string function" Watch "SQL string function" New topic
Author

SQL string function

Ronan Dowd
Ranch Hand

Joined: Jan 21, 2006
Posts: 84
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.


SCJP 1.4 | OCWCD JEE 5
Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3817

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.


SCJP 5 | SCWCD 5
[How to ask questions] [Twitter]
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38006
    
  22
Sounds more like something you sort out with a regular expression and the StringBuilder#insert() method.
Tim McGuire
Ranch Hand

Joined: Apr 30, 2003
Posts: 820

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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: SQL string function
 
Similar Threads
parseInt (String s, int radix)
Binary/Octal/Hex and Decimal Number Systems
massive non-Mersenne prime
how to find digits of the number????
Project Euler Problem 25