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 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
up vote 14 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

You can use the following UDF (inline function rather than scalar)

 CREATE FUNCTION dbo.INSTR 
 (
 @str VARCHAR(8000),
 @Substr VARCHAR(1000),
 @start INT ,
 @Occurance INT
 )
 RETURNS TABLE 
 AS 
 RETURN

WITH Tally (n) AS
(
    SELECT TOP (LEN(@str)) ROW_NUMBER()  OVER (ORDER BY (SELECT NULL)) 
    FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)
)

, Find_N_STR as
(
   SELECT 
    CASE WHEN DENSE_RANK() OVER(PARTITION BY @Substr ORDER BY (CHARINDEX(@Substr ,@STR ,N))) = @Occurance 
     THEN MAX(N-@start +1) OVER (PARTITION BY CHARINDEX(@Substr ,@STR ,N) ) 
     ELSE 0 
     END [Loc]
FROM Tally
WHERE CHARINDEX(@Substr ,@STR ,N) > 0 
)

SELECT Loc= MAX(Loc) 
FROM Find_N_STR
WHERE Loc > 0 

How to use:

 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 t.Name_Level_Class_Section  , l.Loc
  from @t t
  cross apply  dbo.INSTR (t.Name_Level_Class_Section, '_',1,2) l
share|improve this answer

PARSENAME was mentioned as a solution if the string did not contain a period. If it does use this modification to change the periods to something else, then parse the value, and then put the periods back

  Select REPLACE(PARSENAME(REPLACE(REPLACE('Jacky_1_B2.00_23','.','~'), '_', '.'), 2),'~','.')
share|improve this answer

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.