Take the 2-minute tour ×
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. It's 100% free, no registration required.

I have the following table:

==========================================================
|  Name_Level_Class_Section   |   Phone Num              |
==========================================================
|  Jacky_1_B2_23              |  1122554455              |
|  Johnhy_1_B2_24             |  1122554455              |
|  Peter_2_A5_3               |  1122554455              |
==========================================================

I am thinking of simplified my SQL statement as follows:

select 
    *, 
    substring(Name_Level_Class_Section, 
              CHARINDEX('_',Name_Level_Class_Section,
              (CHARINDEX('_', Name_Level_Class_Section) + 1)) + 1, 
      CHARINDEX('_',Name_Level_Class_Section,
     (CHARINDEX('_',Name_Level_Class_Section,
     (CHARINDEX('_',Name_Level_Class_Section)+1))+1))-    
      CHARINDEX('_',Name_Level_Class_Section,
     (CHARINDEX('_',Name_Level_Class_Section)+1))) as CLA 
from 
    Bookings 
order by 
    CLA asc, Name_Level_Class_Section asc

So that when I perform the SQL, it will give me the following result:

==========================================================
|  Name_Level_Class_Section   |  Phone Num     |  CLA    |
==========================================================
|  Jacky_1_B2_23              |  1122554455    |  B2     |
|  Johnhy_1_B2_24             |  1122554455    |  B2     |
|  Peter_2_A5_3               |  1122554455    |  A5     |
==========================================================

Is there any way to simplify my SQL?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

You can use cross apply and the third parameter of charindex to get the position of the underscores.

declare @T table
(
  Name_Level_Class_Section varchar(25)
)

insert into @T values
('Jacky_1_B2_23'),
('Johnhy_1_B2_24'),
('Peter_2_A5_3')

select substring(Name_Level_Class_Section, P2.Pos + 1, P3.Pos - P2.Pos - 1)
from @T
  cross apply (select (charindex('_', Name_Level_Class_Section))) as P1(Pos)
  cross apply (select (charindex('_', Name_Level_Class_Section, P1.Pos+1))) as P2(Pos)
  cross apply (select (charindex('_', Name_Level_Class_Section, P2.Pos+1))) as P3(Pos)

Result:

-------------------------
B2
B2
A5

Update: Using your table the query would look like this:

select *, 
       substring(Name_Level_Class_Section, P2.Pos + 1, P3.Pos - P2.Pos - 1) as CLA
from Bookings
  cross apply (select (charindex('_', Name_Level_Class_Section))) as P1(Pos)
  cross apply (select (charindex('_', Name_Level_Class_Section, P1.Pos+1))) as P2(Pos)
  cross apply (select (charindex('_', Name_Level_Class_Section, P2.Pos+1))) as P3(Pos)
order by CLA asc,
         Name_Level_Class_Section asc

Update 2:

If you know for sure that your value never contains a period . and that it is always a four part name you can use parsename.

select *, 
       parsename(replace(Name_Level_Class_Section, '_', '.'), 2) as CLA
from Bookings
order by CLA asc,
         Name_Level_Class_Section asc
share|improve this answer
    
Is it possible to use a single sql statement? –  Jack May 17 '13 at 6:26
    
@Jack Yes... this is a single statement. The table variable @T is just here to hold the values for testing purposes. Replace @T with Bookings and you are using your table instead of @T. –  Mikael Eriksson May 17 '13 at 6:27
    
I will mark your answer correct next week if I could not find a better answer that can simplified the sql statement. –  Jack May 17 '13 at 9:34
    
@Jack I added another way that is has a lot less code but some restrictions attached. –  Mikael Eriksson May 17 '13 at 9:41
    
Wow, your update 2 was instead short and sweet. Looks like it will be difficult for other people to provide a even shorter one as a answer. –  Jack May 17 '13 at 9:46

Your Answer

 
discard

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.