SQL/TOAD question

Factual Questions
1

Okay, I’ve been racking my brains out trying to figure this out and I’ve searched the web, to little avail.

What I’m trying to do is pull in SSN numbers that are in the system as ‘123456789’ so that it reads like this: ‘XXX-XX-6789’.

currently I have two ‘attempts’:

SELECT
SUBSTR (‘XXX’, 1,3)|| ‘-’||SUBSTR (‘XX’, 4, 2)||’-’ || SUBSTR (SSNCOLUMNNAME, 6, 4) AS “SSN”
FROM BLAH

This does not yield what I want, instead it yields, curiously:

‘XXX–6789’

NEXT:

SELECT
LPAD(SUBSTR(SSNCOLUMNNAME,6,4),9,‘X’) AS “SSN”
FROM BLAH

This yields

‘XXXXXX6789’

Again, close.

What am I missing?

2

Select
substr (ssncolumnname, 1,3)|| ‘-’||substr (ssncolumnname, 4, 2)||’-’ || substr (ssncolumnname, 6, 4) as “ssn”
from blah

3

Sorry, I thought you wanted the full SSN. Try:

Select
‘XXX-XX-’ || substr (ssncolumnname, 6, 4) as “ssn”
from blah

4

My GAWD…It works!

Thank you!

No need for me to continue to rip my hair out.

5

In this case, the error is in the way the SUBSTR function is being used. The basic syntax for the SUBSTR function is:



SUBSTR(somestring, start, length)

So, breaking your code down, you’re saying:



SUBSTR ('XXX', 1,3)|| '-'

Take the string ‘XXX’ (snerk, sorry), start from position 1, and take 3 characters, which gives you ‘XXX’ (snerk, sorry). Tack on a ‘-’ to that, so then we have ‘XXX-’


SUBSTR ('XX', 4, 2)||'-'

Now add to that. Take the string ‘XX’, start from position 4, and take… hang on. There’s no position 4 with this string (it’s only 2 characters long), so you’re going to get nothing, and you’re still stuck with ‘XXX-’. Then tack on another ‘-’, so you get ‘XXX–’


SUBSTR (SSNCOLUMNNAME, 6, 4)

Add to that again. Take the SSN string, start from position 6, and take 4 characters, so we get ‘XXX–6789’.

Does that make sense? If you wanted this to work this way (which is admittedly much less elegant than the solution outlined by JeffB), you’d need something like this:


SELECT 
SUBSTR ('XXX', 1,3)|| '-'||SUBSTR ('XX', 1, 2)||'-' || SUBSTR (SSNCOLUMNNAME, 6, 4) AS "SSN"
FROM BLAH

But the whole point of a function like SUBSTR is to have changing values based on what you’re currently looking at. If what you want is a fixed string like ‘XXX-XX-’ then use that, rather than constructing it on the fly. Your SQL will be simpler, easier to understand and faster.

Also, a point of style - if you’re concatenating strings like this, it will be simpler to read if you use spaces between the strings that are being joined up, like so:


SELECT 
SUBSTR('XXX', 1,3) || '-' || SUBSTR('XX', 1, 2) || '-' || SUBSTR(SSNCOLUMNNAME, 6, 4) AS "SSN"
FROM BLAH

A minor point, but debugging SQL can be hard enough without having to squint at it!