SQL help converting format xxxxxx to xx-xx-xx

Morpheus

Hi,

I need to convert a number from 112233 to 11-22-33 in SQL as part of a select statement.

eg....

SET @sortCode = SELECT SortCode FROM tblBankAccount

-- code to convert @sortCode from 112233 to 11-22-33


INSERT INTO tblNewTable
VALUES (@sortCode, etc)

how would i do this?

thanks

masterclass

Hi Morpheus,

Does this help you ?

select 933406 from dual

select substr(933405,0,2) || '-' || substr(933405,3,2) || '-' || substr(933405,5,7) from dual

liamo

Hi,

If this is not going to be a once-off requirement, I prefer to put stuff like this into a UDF so that it makes the SQL Statement a bit more readable.

Create Function udf_SortCode ( @SortCodeIn Char(6) )
Returns Char(8)
As
Begin
  Declare @SortCodeOut Char(8)
  Set @SortCodeOut = Substring(@SortCodeIn,1,2) + '-' + 
     Substring(@SortCodeIn,3,2)  + '-' + Substring(@SortCodeIn,5,2) 
  Return @SortCodeOut
End

Then you can do the following :

Select dbo.udf_SortCode(SortCode) FROM tblBankAccount

Regards,

Liam

PS In the absence of information to the contrary, I've assumed that you're using SQL Server.

Morpheus

cheers, thats what i was looking for.

thanks lads