Trim Only the comma separated Numbers without trimming the character appended Numbers

Question

I have a column '<InstructorID>' which may contain data like "79,instr1,inst2,13" and so on. The following code gives me result like this "791213"

declare @InstructorID varchar(100)
set @InstructorID= (select InstructorID from CourseSession where CourseSessionNum=262)
WHILE PATINDEX('%[^0-9]%', @InstructorID) > 0
BEGIN
    SET @InstructorID = STUFF(@InstructorID, PATINDEX('%[^0-9]%', @InstructorID), 1, '')
END
select @InstructorID

I need the output ti be like this "79,13" i.e those numbers attached to characters shoud not appear in output.

P.S: I need to achieve this using sql only. Unfortunately i'm unable to use Regex which would have made this task much easier.

You should fix the data format so you are not storing lists of ids as a comma-separated string. These should be separate rows in a junction table. And, please tag your question with the database you are using (presumably SQL Server). — Gordon Linoff, May 19 '15 at 12:00
instead of checking for regular expression check for isNumeric()? Perhaps using a method like [this](http://stackoverflow.com/questions/10031658/comma-separated-list-in-sql) to walk the string — xQbert, May 19 '15 at 12:04
@Gordon I agree.This column stores Id's which are referenced in other table. However user may add some temporary instructors which are not present in the other table hence the name is saved. The database is very huge and doing database changes now seems very difficult;in addition the site is not owned by me. — Vishal prabhu lawande, May 19 '15 at 12:04

score 1 · Accepted Answer · answered May 19 '15 at 12:20

I agree with others that your problem would seem to be indicative of a mistake in your data design.

However, accepting that you cannot change the design, the following would allow you to achieve what you are looking for:

DECLARE @InstructorID   VARCHAR(100)
DECLARE @Part   VARCHAR(100)
DECLARE @Pos    INT
DECLARE @Return VARCHAR(100)
SET @InstructorID = '79,instr1,inst2,13'
SET @Return = ''

-- Continue until InstructorID is empty
WHILE (LEN(@InstructorID) > 0)
BEGIN
    -- Get the position of the next comma, and set to the end of InstructorID if there are no more
    SET @Pos = CHARINDEX(',', @InstructorID)
    IF (@Pos = 0)
        SET @Pos = LEN(@InstructorID)

    -- Get the next part of the text and shorted InstructorID
    SET @Part = SUBSTRING(@InstructorID, 1, @Pos)
    SET @InstructorID = RIGHT(@InstructorID, LEN(@InstructorID) - @Pos)

    -- Check that the part is numeric
    IF (ISNUMERIC(@Part) = 1)
        SET @Return = @Return + @Part
END

-- Trim trailing comma (if any)
IF (RIGHT(@Return, 1) = ',')
    SET @Return = LEFT(@Return, LEN(@Return) - 1)

PRINT @Return

Essentially, this loops through the @InstructorID, extracting parts of text between commas. If the part is numeric then it adds it to the output text. I am PRINTing the text but you could SELECT it or use it however you wish.

Obviously, where I have SET @InstructorID = xyz, you should change this to your SELECT statement.

This code can be placed into a UDF if preferred, although as I say, your data format seems less than ideal.

This works but using a loop to split strings is horrible inefficient. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, May 19 '15 at 13:44

Trim Only the comma separated Numbers without trimming the character appended Numbers

1 Answers1