473,326 Members | 2,134 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

SQl Help - CHARINDEX on Multiple records

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
Jun 13 '06 #1
0 1543

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: M Wells | last post by:
Hi All, Just wondering if it's any more efficient to use the following SQL statement to return a partial match from a column as opposed to the second statement. SELECT * FROM Table1 WHERE...
2
by: Little PussyCat | last post by:
Hello, I need to be able to replace only the first occurance of a space character in a column. Reason being is the data in the column I am trying to replace seems to have umpteen space...
5
by: Willem | last post by:
Hello I am quite hopeless and of course a newbe. The situation: Sql2k / query I would like it ot break down the following string: 2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094
3
by: csomberg | last post by:
SQL Server 2000 Ya know, it is always the simplest stuff that gets ya !! I am having the hardest time getting a simple piece of code working. Must be brain dead today. Goal: Get the users...
0
by: prav78 | last post by:
hi there i have a field name(fil_srt_cond) with the values of cmpnt_name,ASC,1,2 cmpnt_stuff,DESC,2,3 i used the char index:- ...
1
by: chudson007 | last post by:
I have a field containing a string with '/' in it multiple times. How can I return the charindex of the last occurance of '/' in the string? Regards, Ciarán
1
by: db55 | last post by:
This script doesn't work. Why? UPDATE SET = LTRIM(SUBSTRING(, 1, convert(bigint, CHARINDEX(',', Comments)-1))) WHERE NOT( IS NULL) AND LEN() > 8 Basically, I'm trying to...
2
by: matthewwhaley | last post by:
What is the best way to essentially use the charindex(find) function if the value is could be more than one variable (A or B or C) I can't seem to get an "or", "if" or "select if" to work ...
7
by: iritchie | last post by:
Hello all, I am trying to write a script which breaks down a single address field into individual fields, with char(10) or a carriage return as the delimiter. "empltable" is the table I am...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.