Help me with SQL query

[d00_ape]

I need to populate a new colum with data from one other column in the same table:

Table Has columns id, name, part:
Id name part
"122" "E3, SS, ABC" "NULL"
"123" "E3, BB, JKL" "NULL"
… … …

I want to have a script that fills the part column with the string to the right of ‘,’ from the name column. The result should look like this:

Id name part
"122" "E3, SS, ABC" "ABC"
123" "E3, BB, JKL" "JKL"
… … …

[davide++]

Hi all.

You didn't say what db you're using, and what you can do depends on it.
Anyway, you should use the string functions. For example, if your db was Oracle you would write


UPDATE MYTABLE
SET PART = SUBSTR(NAME, INSTR(NAME, ', '))


I hope this will help you.

[d00_ape]

I'm using SQL Server 2005 but I'll try that!

[Alsvha]

Well - from your example, you want the right most value? Because there are multiple commas in your example:

So if you have "E3, SS, ABC" and you want "ABC" returned, then it is easiest to reverse the string and work on that.
This means you can end up with something like this:
(this works in SQL Server 2005)


UPDATE YourTable
SET PART =
(SUBSTRING(name, LEN(name) - (CHARINDEX(',', REVERSE(name)) - 1) + 1,LEN(name) - (CHARINDEX(',', REVERSE(name)))))


Now this might look confusion at first, but if we split it up, then what it does is that the code
CHARINDEX(',', REVERSE(name)) - 1 finds the position of the last comma and moves 1 (to avoid getting the comma).
The LEN(name) subtracts to get the remaining length of the string.

You can split it up with some intermediate results if needs be for easier overview of the code.

Depending on whether you have additional spaces in the name and don't want/need them, you can RTRIM and LTRIM the result.

[d00_ape]

Perfect!!

You can split it up with some intermediate results if needs be for easier overview of the code.
Do you have any idea of how to do this in a good way. As you might guess my SQL knowlege is at minimum...

[d00_ape]

I modified it like this:
UPDATE MyTable
SET TypeSymbol =
LTRIM(
RTRIM(
SUBSTRING(
Name,
LEN(Name) - (CHARINDEX(',', REVERSE(Name)) - 1) + 1,
LEN(Name)
)
)
)