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