CODEWITHSUNDEEP
sqlstringoracle-databaseoracle10g
nurgan
nurgan

Reputation: 329

String concatenation in query, filling string to fixed length

I'm operating on a Oracle 10g database. The table has a column CODE which is the Primary key, and is a varchar. The Field is always in the Format "xx xxx xx", for example "20 965 04", with the first and the second an number that is not relevant and the last part a consecutive number.

What i have to do is, doing an INSERT with the first two parts of the key remaining the same and the last part beeing one more than the highest consecutive number for These specific first to parts.

So, i startet playing a Little, and my first Approach was like:

SUBSTR('20 031 03', 0, 7)||(TO_CHAR(TO_NUMBER( 
 SUBSTR(
   (SELECT MAX(CODE )
   FROM table tmk
   WHERE tmk.CODE LIKE SUBSTR('20 031 03', 0, 6)||'%')
  , 8,  9) ) + 1
 ))

This Approach is already not beautiful, but in my opinion OK. But then i realised that, when the consecutive number is below 10, after the TO_NUMBER and the TO_CHAR it is of cource for example 5 instead of 05. So if I have "20 965 04", what i get after this is "20 965 5" but I Need "20 965 05"!

So, the only way that i could think of is an case Statement checking the length of the string after increasing the number, and adding the 0 if neccecary:

SELECT
CASE (LENGTH(TO_CHAR(TO_NUMBER( SUBSTR(
   (SELECT MAX(CODE)
   FROM table tmk
   WHERE tmk.CODE LIKE SUBSTR('20 031 03', 0, 6)||'%')
  , 8, 9) ) + 1
 )))
WHEN 1 THEN
SUBSTR('20 031 03', 0, 7)||'0'||(TO_CHAR(TO_NUMBER( 
 SUBSTR(
   (SELECT MAX(CODE)
   FROM table tmk
   WHERE tmk.CODE LIKE SUBSTR('20 031 03', 0, 6)||'%')
  , 8,  9) ) + 1
 ))
ELSE
SUBSTR('20 031 03', 0, 7)||(TO_CHAR(TO_NUMBER( 
 SUBSTR(
   (SELECT MAX(CODE)
   FROM table tmk
   WHERE tmk.CODE LIKE SUBSTR('20 031 03', 0, 6)||'%')
  , 8,  9) ) + 1
 ))
END
FROM dual;

So this works, but is something like the most ugly SQL I have ever seen. But since I am currently somehow stuck at it, does anybody have a nicer and shorter Approach to do the same Thing?

Upvotes: 0

Views: 2676

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

The TO_CHAR function has a format paramter you can use:

SUBSTR('20 031 03', 1, 7) || 
TO_CHAR(
  TO_NUMBER( 
   SUBSTR(
     (SELECT MAX(CODE) FROM table tmk WHERE tmk.CODE LIKE SUBSTR('20 031 03', 1, 6) || '%')
   , 8,  2) + 1
  )
, 'FM00')

I changed your SUBSTR parameters slighly. Indexes start with 1. And you want a last number of two digits.

Upvotes: 0

bhamby
bhamby

Reputation: 15450

Have a look at the LPAD function.

For example, SELECT LPAD(1,2,'0') FROM DUAL will return 01.

Upvotes: 2

Related Questions