SQL Help

1. Senior Member
Join Date
Dec 1969
Posts
322

## SQL Help

Hi Folks,<BR> I am trying to use the decode function. I have a nine digit number stored in a table. However if this number beings with 92 I wish to switch it to 29 and add a -S to the end of the number. So 923456789 will become 293456789-S. I tried to do it this way:<BR><BR>select decode(substr(num,1,2),&#039;92&#039;,&#039;29&#03 9;) &#124&#124 substr(num,3,7) &#124&#124 &#039;-S&#039; from totals<BR><BR>but all this does is chop off the srat of every number and add and S to it. But if the number does not begin with 92 I want it to be elft alone. Any help is much appreciated.<BR><BR>Cheers,<BR><BR>Neil

2. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## Sounds like Oracle...

.. right?

3. Senior Member
Join Date
Dec 1969
Posts
2,854

## untested

Select Case left(cast(num as varchar(10)), 2)=&#039;92&#039; BEGIN &#039;29&#039; + ast(num as varchar(10), 7) +&#039;-S&#039; Else Num End as Num from totals<BR><BR>HTH.<BR>D.

4. Senior Member
Join Date
Dec 1969
Posts
2,854

## correction

Select Case left(cast(num as varchar(10)), 2)=&#039;92&#039; BEGIN &#039;29&#039; + Cast(num as varchar(10)), 7) +&#039;-S&#039; Else cast(Num as varchar(10)) End as Num from totals

5. Senior Member
Join Date
Dec 1969
Posts
2,854

## one more try...

Select Case left(cast(num as varchar(10)), 2)=&#039;92&#039; BEGIN &#039;29&#039; + right(Cast(num as varchar(10)), 7) +&#039;-S&#039; Else cast(Num as varchar(10)) End as Num from totals

6. Senior Member
Join Date
Dec 1969
Posts
322

## RE: one more try...

Thanks for your help much appreciated i&#039;ll give that a shot.

7. Senior Member
Join Date
Dec 1969
Posts
3,195

## RE: SQL Help

Simply add the "else" part for whatever you want on your decode.<BR><BR>Decode reads like an if statement. <BR><BR> decode(something,if this1,then this1,if this2,then this2, etc., ELSE)<BR><BR>See the very last part is your else so put the value/column you want it to default to.<BR><BR>As an aside, Oracle 8i and greater supports the CASE statement in dynamic SQL(but not PL/SQL) which may be easier to read.<BR><BR>Good luck<BR>Pete

8. Senior Member
Join Date
Dec 1969
Posts
3,195

## Or using decode

select decode(substr(num,1,2),&#039;92&#039;,&#039;29&#03 9;,to_char(Num)) &#124&#124 substr(num,3,7) &#124&#124 &#039;-S&#039; from totals <BR><BR>Just in case you are doing this in a stored proc. ; )<BR><BR>Pete

9. Senior Member
Join Date
Dec 1969
Posts
2,854

## OK, debugged and tested and works.

<BR>SELECT CASE WHEN LEFT(cast(num AS varchar(10)), 2) = &#039;92&#039; THEN &#039;29&#039; + RIGHT(Cast(num AS varchar(10)), 7) + &#039;-S&#039; ELSE cast(Num AS varchar(10)) <BR> END AS Num<BR>FROM totals<BR>

10. Senior Member
Join Date
Dec 1969
Posts
11,247

## Bold and impressive

Makes two assumptions<BR>1) the field is numeric (mshould not be if it is a telephone number)<BR>2) only ever 10 digits!<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•