File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL string function

 
Ronan Dowd
Ranch Hand
Posts: 84
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Vijitha Kumara
Bartender
Posts: 3908
9
Chrome Fedora Hibernate
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Campbell Ritchie
Sheriff
Pie
Posts: 47258
52
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sounds more like something you sort out with a regular expression and the StringBuilder#insert() method.
 
Tim McGuire
Ranch Hand
Posts: 820
IntelliJ IDE Tomcat Server VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic