query help

[Bell1991]

I have a column that has contains question number. A question can have 2a, 2b type order. How can i seperate the two? I would like to return: question, question order, question sub order.

Thanks in advance.

 

[gmmastros]

There are probably several ways to do this.

My approach was to use PatIndex to search for the first character in the string that is NOT a number between 0 and 9. I then use this value in conjunction with the LEFT and RIGHT function to separate the parts.

ex:

Declare @Temp Table(Question VarChar(20))

Insert Into @Temp Values('1')
Insert Into @Temp Values('2a')
Insert Into @Temp Values('21b')

Select Case When PatIndex('%[^0-9]%', Question) > 0 
            Then Left(Question, PatIndex('%[^0-9]%', Question)-1)
            Else Question
            End As QuestionNumber,
       Case When PatIndex('%[^0-9]%', Question) > 0 
            Then Right(Question, Len(Question) - PatIndex('%[^0-9]%', Question) + 1)
            Else ''
            End As QuestionOrder
From   @Temp

As you can see, this is rather ugly. As such, I encourage you to separate this one column in to 2 columns. This should be a permanent change to your data structure.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JohnDTampaBay]

Will the order portion (a,b,etc) of the QuestionNumber always be a single character at the end? Do all QuestionNumber values have an order? Here's a little something to start with:

SELECT
  QuestionNumber,
  Right(QuestionNumber,1) AS QuestionOrder,
  Replace(QuestionNumber, Right(QuestionNumber,1), '') AS NewQuestionNumber,
FROM YourTable

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[JohnDTampaBay]

Ah, gmmastros beat me to the punch and is much more concise.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)