SQL Format text value -> Format(ProjectNo, '########.###.###')?

Transact-SQL
1

Good day, I am looking for the parameters for formatting a text value into numbers. This should be displayed as, for example: 20180026.010.001

Format(ProjectNo, '########.###.###')
-> does not work properly, I get the message "Incorrect syntax near '.'."

Anyone an idea how I can solve this?
Kind regards

2

If the value always has 14 digits to start with, then:

STUFF(STUFF(ProjectNo, 12, 0, '.'), 9, 0, '.')

1 Like
3

hi, thanks for your response.

No unfortunately, subnumbering has only been chosen when the projects has sub projects.

The main project always has 9 characters, the sub project then total 12 characters and the sub-sub project in total 15 characters. Always in this format: 123456789.001.001 / ########.###.###

201801218
201801218.012
201801218.012.001

Can I solve this with something like length is then . . .

Regards

4 

SELECT ProjectNo,
    CASE 
    WHEN LEN(ProjectNo) > 12 THEN STUFF(STUFF(ProjectNo, 13, 0, '.'), 10, 0, '.')
    WHEN LEN(ProjectNo) >  9 THEN STUFF(ProjectNo, 10, 0, '.')
    ELSE CAST(ProjectNo AS varchar(17)) END AS ProjectNoFormatted
FROM ( VALUES(CAST(201801218 AS bigint)), (201801218012), (201801218012001) ) 
    AS x(ProjectNo)