Mike R Mike R - 1 year ago 74
SQL Question

SQL - Parsing data value and assigning to another column

Let's say I have a table students, and under that one of the columns are "Facebook Profile Link" and "Facebook ID".

Under Facebook Profile Link, it contains the full URL of the student's facebook account, for example: "https://facebook.com/john.smith".

How can I easily do an update to the column "Facebook ID", to only contain john.smith and not the full url?

I can do it one by one, but the problem is, we do have dozens of records of students under this table already, so a mass update could really help.


Answer Source
update      MyTable

set         "Facebook ID" = right("Facebook Profile Link",charindex('/',reverse("Facebook Profile Link"))-1)

Small demo

declare @str varchar(100) = 'https://facebook.com/john.smith'

select right(@str,charindex('/',reverse(@str))-1)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download