Correcting Data Format

Hi,

pls try something like this

SELECT  CASE WHEN LEN(Matter) = 9 THEN SELECT Right('00000000' + CONVERT(NVARCHAR,LEFT(Matter, 7)), 8)+'-'+Right('00000000' + CONVERT(NVARCHAR,RIGHT(Matter, 1)), 8) ELSE Matter END as 'Result'
FROM    TableName 

Select allOpen in new window

Regards

Using an APPLY operator allows us to reuse column aliases in the select clause. Here the apply operator supplies the length (l) and the delimiter position (pos). In the apply operator I use PATINDEX to locate the first non-numeric character.

select
    case when l < 17 then right('00000000' + left(Matter,pos-1),8) + '-' + right('00000000' + right(Matter,l-pos),8)
         else Matter
    end as Matter
from (
    select '2158567/1' as Matter
    union all
    select '01258897-00000001'
    union all
    select null
    ) as s
cross apply (
    select len(Matter) l, patindex('%[^0-9]%',Matter) pos
   ) as a

Select allOpen in new window

| Matter            |
| ----------------- |
| 02158567-00000001 |
| 01258897-00000001 |
|  null             |

Select allOpen in new window

See http://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=917a28a5309c65a480379fdf3d486937

If you can be 100% sure that the numbers can only be formatted in three or four ways, I would suggest -

UPDATE [YourTable]
REPLACE([YourColumn], '-', '-0000000')
WHERE LEN([YourColumn]) < 17

UPDATE [YourTable]
REPLACE([YourColumn], '/', '-0000000')
WHERE LEN([YourColumn]) < 17

etc...

However, if the number on the end can be more than one digit (12345678/12), then my solution will not work. Likewise, my solution won't work if there are any numbers which deviate from any of the three formats that you describe.

Corrected

SELECT  CASE WHEN LEN(Matter) = 9 THEN Right('00000000' + CONVERT(NVARCHAR,LEFT(Matter, 7)), 8)+'-'+Right('00000000' + CONVERT(NVARCHAR,RIGHT(Matter, 1)), 8) ELSE Matter END as 'Result'
FROM    TableName 

Select allOpen in new window

Thanks for the answers I will try them today

Hi,
Any update on the above?