Heren - 1 month ago 11

SQL Question

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:**

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

is 2, .123 will end up as .23)