Split column in MS SQL an copy to new columns

Transact-SQL
1

I have a column with Swedish personal identification numbers, which means it is the date of birth + 4 more digits. 198511028954
The column also includes foreign personal identification numbers without the last 4 digits.
The column is now varchar(12)

I want to split the personal identification number and copied the first 8 digits of the personal identification number to a separate column (date) and the rest to another column.

Any tips on how to do it?

2

you can use the substring function to get the leading and trailing characters from the string.

Declare @PIN varchar(12)='198511028954'

SELECT SUBSTRING(@PIN ,1,8) as 'Date'

SELECT SUBSTRING(@PIN ,9,4) as 'ForeignNumber'

1 Like
3

Thanks
I use this:

UPDATE table
SET born = SUBSTRING(persnr, 1, 8)

But i get this: Conversion failed when converting date and/or time from character string.

born have dbtype date, and persnr have dbtype varchar(12)

4

as born is of data type date, you are performing an implicit conversion with

born = SUBSTRING(persnr, 1, 8)

The error message means there are some value in SUBSTRING(persnr, 1, 8) that can't be convert to date

You can use try_convert() that will return NULL if it is unable to perform the convert

born = try_convert(date, SUBSTRING(persnr, 1, 8))

after that check those rows with NULL value and verify the persnr and fix it manually

5

Thanks, now i found the problem :slight_smile:

6

Speaking of problems, are there no rules or regulations against storing such information as date of birth and personal identification numbers in plain text in Sweden?

7

Personal identification numbers and coordination numbers are not considered sensitive personal data according to the Data Protection Regulation, but are considered to be of extra protection value.

8

You mean in Sweden, correct?