jMarcel jMarcel - 2 months ago 5
SQL Question

T-SQL Create Id column from a Name column

I'm trying to separate the name column and create an id column as below:

myTable:

name
John F1234567
Mary F7654321
...


What I've done until now is:

select RIGHT(name,8) as id, name FROM myTable


Result:

id name
F1234567 John F1234567
F7654321 Mary F7654321


But the result I need is:

id name
F1234567 John
F7654321 Mary


I've used some string functions, but no success.

Can anyone help me, please?

Thanks in advance.

Answer

You can try using replace function

select RIGHT(name,8) as id, replace(name, right(name,8), '') as name FROM myTable