Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)
share|improve this question
Can you post some sample code with expected results ? – Kin Jul 23 '13 at 13:01
updated @Kin, thanks – 0xor1 Jul 23 '13 at 13:09
up vote 3 down vote accepted
DECLARE @x TABLE(StrippedLogin VARCHAR(255));

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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