# SQL/TOAD question

**URL:** <https://boards.straightdope.com/t/sql-toad-question/624310>\
**Category:** Factual Questions\
**Created:** [June 6, 2012, 8:17pm UTC](https://boards.straightdope.com/t/sql-toad-question/624310 "2012-06-06T20:17:53Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Meatros](https://avatars.discourse-cdn.com/v4/letter/m/b4bc9f/32.png) [@Meatros](https://boards.straightdope.com/u/Meatros)\
**Post date:** [June 6, 2012, 8:17pm UTC](https://boards.straightdope.com/t/sql-toad-question/624310/1 "2012-06-06T20:17:53Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![JeffB](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/jeffb/32/188_2.png) [@JeffB](https://boards.straightdope.com/u/JeffB)\
**Post date:** [June 6, 2012, 8:26pm UTC](https://boards.straightdope.com/t/sql-toad-question/624310/2 "2012-06-06T20:26:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JeffB](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/jeffb/32/188_2.png) [@JeffB](https://boards.straightdope.com/u/JeffB)\
**Post date:** [June 6, 2012, 8:27pm UTC](https://boards.straightdope.com/t/sql-toad-question/624310/3 "2012-06-06T20:27:42Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![Meatros](https://avatars.discourse-cdn.com/v4/letter/m/b4bc9f/32.png) [@Meatros](https://boards.straightdope.com/u/Meatros)\
**Post date:** [June 6, 2012, 8:32pm UTC](https://boards.straightdope.com/t/sql-toad-question/624310/4 "2012-06-06T20:32:40Z")

</div>

> [@JeffB](#):
>
> Sorry, I thought you wanted the full SSN. Try:
> 
> Select  
> ‘XXX-XX-’ || substr (ssncolumnname, 6, 4) as “ssn”  
> from blah

My GAWD…It works!

Thank you!

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

---

<div class="post-metadata">

**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\
**Post date:** [June 7, 2012, 8:04am UTC](https://boards.straightdope.com/t/sql-toad-question/624310/5 "2012-06-07T08:04:10Z")

</div>

> [@Meatros](#):
>
> 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’
> 
> What am I missing?

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

```auto

SUBSTR(somestring, start, length) 

```

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

```auto

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-’

```auto

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–’

```auto

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:

```auto

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:

```auto

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!
