Sql-server – Altering strings while selecting them

selectsql server

I'm working in SQL Server 2008, I have a varchar(255) column in my table, I want to select out all of the strings which contain the ':' character, and select the first part of the string before (but not including) the ':' character into a table variable so I can then work with this new data set. I have a piece of code which selects the substring I want from a string variable but I don't know how to do this as part of selecting all of the required strings in one go from the original table.

IF PATINDEX('%:%',@Login) > 0 
BEGIN 
    set @Login = SUBSTRING(@Login,0,PATINDEX('%:%',@Login)) 
END

I would prefer to not to have to loop over the table but I am not experienced with sql and so am not sure what is possible and what is not

So if I have in my original table:

Login varchar(255)
-----
dn\jblogs
dn\frobins:21 jul 2013
dn\tbecker
dn\ghammer:17 jul 2013

then the script should pull out into a table variable:

StrippedLogin varchar(255)
-------------
dn\frobins
dn\ghammer

Best Answer

DECLARE @x TABLE(StrippedLogin VARCHAR(255));

INSERT @x(StrippedLogin)
  SELECT LEFT([Login], CHARINDEX(':', [Login])-1)
  FROM dbo.OriginalTable
  WHERE CHARINDEX(':', [Login]) > 1;
Related Question