Split text in a column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Split text in a column

Author

Topic

texas1992
Starting Member

21 Posts

Posted - 2012-08-16 : 10:51:53

I have a field that contains names in the form Last, First. I need to switch this to be First Last.

This has to be done in the stored procedure. How do I do this?


	SELECT fo.timekeeperid, FO.Color,FO.XPosition,FO.YPosition,
	FO.ObjectID,FO.Name,FO.objecttype,fo.locationcode,FO.[description]
	FROM FLOORPLANOBJECTS FO LEFT OUTER JOIN FLOORPLANLOCATIONCODES FLC ON FO.LocationCode = FLC.LocationCode
	WHERE fo.FLOORID = @FloorID AND LayoutName = @LayoutName

The field is FO.Name

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-16 : 11:10:05

Assuming that all names are of the form last, first

SELECT fo.timekeeperid, FO.Color,FO.XPosition,FO.YPosition,
FO.ObjectID,
Name = right(FO.Name,charindex(',',reverse(FO.Name))-2) + ' ' + left(FO.Name,charindex(',',FO.Name)-1)
FO.objecttype,fo.locationcode,FO.[description]
FROM FLOORPLANOBJECTS FO LEFT OUTER JOIN FLOORPLANLOCATIONCODES FLC ON FO.LocationCode = FLC.LocationCode
WHERE fo.FLOORID = @FloorID AND LayoutName = @LayoutName

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

texas1992
Starting Member

21 Posts

Posted - 2012-08-16 : 13:13:37

Thanks for your reply.

I have put the SQL into my stored proc but I am getting errors


	SELECT FO.timekeeperid, FO.Color,FO.XPosition,FO.YPosition,FO.ObjectID,
	Name = right(FO.Name,charindex(',',reverse(FO.Name))-2) + ' ' + left(FO.Name,charindex(',',FO.Name)-1)
	FO.objecttype,FO.locationcode,FO.[description]
	FROM FLOORPLANOBJECTS FO LEFT OUTER JOIN FLOORPLANLOCATIONCODES FLC ON FO.LocationCode = FLC.LocationCode
	WHERE fo.FLOORID = @FloorID AND LayoutName = @LayoutName

They all have the same error:
"The multi-part identifier "FO.Color" could not be bound". The fields that have the errors are underlined above.

I am not a DBA so can you help with this?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-16 : 14:44:35

we cant suggest much without knowing which tables the columns come from. Make sure you use correct aliases for columns to represent source tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

texas1992
Starting Member

21 Posts

Posted - 2012-08-16 : 15:41:20

I don't understand...the tables are listed in the query.

If I remove the line:


Name = right(FO.Name,charindex(',',reverse(FO.Name))-2) + ' ' + left(FO.Name,charindex(',',FO.Name)-1)

everything works great.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-08-16 : 15:58:06

notice the comma in red

SELECT FO.timekeeperid, FO.Color,FO.XPosition,FO.YPosition,FO.ObjectID,
Name = right(FO.Name,charindex(',',reverse(FO.Name))-2) + ' ' + left(FO.Name,charindex(',',FO.Name)-1),
FO.objecttype,FO.locationcode,FO.[description]
FROM FLOORPLANOBJECTS FO LEFT OUTER JOIN FLOORPLANLOCATIONCODES FLC ON FO.LocationCode = FLC.LocationCode
WHERE fo.FLOORID = @FloorID AND LayoutName = @LayoutName

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

texas1992
Starting Member

21 Posts

Posted - 2012-08-16 : 16:00:29

That was it. Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources