insert char into string at multiple positions sql

Question

how would i like to convert accounting GL number

99999999999999999

to

999-99999-99-9999.999

in a query to MSSQL server 2005

i dont need to update the data, just have the STRING be converted on query.

Table: GLM_MASTER__ACOUNT Field: Account

thanks.

Answer 1

You need to use SUBSTRING:

SELECT
    SUBSTRING(account, 1, 3) + '-' +
    SUBSTRING(account, 4, 5) + '-' +
    SUBSTRING(account, 9, 2) + '-' +
    SUBSTRING(account, 11, 4) + '.' +
    SUBSTRING(account, 15, 3)

Answer 2

One more way using STUFF()

DECLARE @a varchar(64)
SET @a = '99999999999999999'
SELECT  STUFF(STUFF(STUFF(STUFF(@a, 4, 0, '-'), 10, 0, '-'), 13, 0, '-'), 18, 0, '.')