Heren Heren - 1 month ago 11
SQL Question

format a wbs column in sql

How to split then format a wbs column to have a prefix of zeroes using SQL?

Example: 1.2.15 to 1.002.015

Sample WBS Column content:

- 1.1
- 1.1.1
- 1.1.2
- 1.1.3
- 1.2

Answer Source

Not the most beautiful code on Earth, but it does the trick:

DECLARE @STR VARCHAR(100) = '1.2.15'
DECLARE @DIGITS INT = 3

DECLARE @P1 VARCHAR(10)
DECLARE @P2 VARCHAR(10)
DECLARE @P3 VARCHAR(10)
DECLARE @P4 VARCHAR(10)

DECLARE @PARTS INT = 1 + LEN(@STR) - LEN(REPLACE(@STR, '.', ''))

SELECT @P1 = PARSENAME(@STR, @PARTS)
SELECT @P2 = PARSENAME(@STR, @PARTS - 1) 
SELECT @P3 = PARSENAME(@STR, @PARTS - 2) 
SELECT @P4 = PARSENAME(@STR, @PARTS - 3)

SELECT @P2 = RIGHT('0000' + CONVERT(VARCHAR(10), @P2), @DIGITS)
SELECT @P3 = RIGHT('0000' + CONVERT(VARCHAR(10), @P3), @DIGITS)
SELECT @P4 = RIGHT('0000' + CONVERT(VARCHAR(10), @P4), @DIGITS)

SELECT ISNULL(@P1, '') + ISNULL('.' + @P2, '') + ISNULL('.' + @P3, '') + ISNULL('.' + @P4, '')
-- Output is 1.002.015

The key is to use the PARSENAME function. It's intended for parsing a fully qualified SQL object name, but here we will use it for WBS. I first find the number of points to know how many parts it has, as that function's second parameter is the part counting from the end. That way I can get the main version in @P1, the next one in @P2, etc.

Then I do a trick to add leading zeroes, and finally I just concatenate the numbers, avoiding nulling the string if one of them is NULL.

Limitations:

  1. Only up to 4 parts (can't use this for 1.2.15.6.3)
  2. Must be valid, no spaces and no . at the end
  3. Number of digits per part is limited to 5 (if needed add more zeros to '0000')
  4. If a part is shorter than the number of digits specified, it gets trimmed (if @DIGITS is 2, .123 will end up as .23)