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