Date: 07/23/07 (SQL Server)    Keywords: database, sql

Another dumb question. At least I give you guys the easy ones, right? SQL2K5, latest SP, etc etc.

I'm trying to pull everything to the right of a comma in a cell. To spare me having to upload data, I've recreated it in a quick procedure using a variable to store the location of one of the cells in the table:


DECLARE @Author CHAR(50)
SET @Author = 'Aarseth, Espen '
PRINT LEN(@Author)
PRINT CHARINDEX(',',@Author)+1
PRINT (LEN(@Author)-(CHARINDEX(',',@Author)+1))
PRINT 'right(' + ltrim(rtrim(@Author)) + ', ' + CAST(LEN(@Author)-(CHARINDEX(',',@Author)+1) AS char(2)) + ') = '
PRINT right(@Author, (LEN(@Author)-(CHARINDEX(',',@Author)+1)))


My problem seems to stem from SQL treating the LEN() and RIGHT() commands differently, as far as I can tell-- LEN(@Author) returns 16, which is the actual length of the data in the field, but when I use RIGHT(), the padding is included, and consequently I get a field full of spaces. I've attempted every combination of LTRIM(), RTRIM() and @Author I can think of, and I still end up with a field full of spaces as a result set. When I declare @Author as char(16), I get the proper results, but in the table itself, there's no way to do that (multiple author names are, for obvious reasons, variable in length) short of using a cursor and going row by row, and I'd rather not do that (I'm not fond of cursors in the first place, and this is a relatively big table for a scan-and-modify routine, so I may run into time concerns).

I vaguely recall there being some variation on the SPLIT() command in T-SQL, but the sieve that is my brain has forgotten what it's called, and searching on "split" in books online is coming up with squadoosh. Anyone know what it is I'm forgetting? (And whether it's possible to use the command to update two to four colums in the database at once?)

Thanks.

Source: http://community.livejournal.com/sqlserver/60987.html