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, 2013 at 13:01

1 Answer 1

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 and acknowledge you have read our privacy policy.

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