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