SQl Help - CHARINDEX on Multiple records

[Laura-Jayne]

Join Date: May 2006
Posts: 6 Help - SQL Query Anaylzer

--------------------------------------------------------------------------------

Hi

I am using the following code to give me everything after a space in a field:

DECLARE @NM CHAR(30)
SET @NM = 'IMOGEN MASON'

SELECT SUBSTRING(@NM, CHARINDEX(' ', @NM) +1, LEN(@NM))
This code returns 'MASON' which is what I am after.

HOWEVER - I need to replace where it says 'Imogen Mason' with a field name so it repeats for each field in the table - there are nearly 3,000 rows so doing it indivisualy isnt an option.

I thought this would work:


DECLARE @NM CHAR(30)
SET @NM = (SELECT [NaME] FROM gola_stats)

SELECT SUBSTRING(@NM, CHARINDEX(' ', @NM) +1, LEN(@NM))


but I get an error that says: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
How do I repeat this for each row in my table? I then need to update another field with what the quyery returns (thats the easy bit)

Any help apreciated, thank you in advance!

Imogen