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 
    set @Login = SUBSTRING(@Login,0,PATINDEX('%:%',@Login)) 

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\frobins:21 jul 2013
dn\ghammer:17 jul 2013

then the script should pull out into a table variable:

StrippedLogin varchar(255)
  • Can you post some sample code with expected results ? – Kin Shah Jul 23 '13 at 13:01
DECLARE @x TABLE(StrippedLogin VARCHAR(255));

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.