Search code examples
sqltypeconverter

Reformat bank account sortcode in SQL

I'm a bit of a novice so be gentle with me.

I'm looking to convert bank account sortcode data (6 digit numeric eg "123456") into the following format "12-34-56"). That is inserting hyphens / dashes between each pair of digits retrieved from the database.

Thanks a lot.

Solution

  • If you are using SQL Server, then You can simply try like,

    SELECT 
CAST(SUBSTRING(CODE,1,2) AS VARCHAR)+'-'+
CAST(SUBSTRING(CODE,3,2) AS VARCHAR)+'-'+
CAST(SUBSTRING(CODE,5,2) AS VARCHAR)
FROM TABLE_NAME

    OR

    You can use, 

    SELECT 
FORMAT(CODE,'##-##-##') 
FROM TABLE_NAME