12

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?

3 Answers 3

27

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
0
2

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),'~','.')
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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