none
CASE

    Question

  • Hi All,

    I have column E_INA23 (varcvhar 254) it has different strings and characters, e.g "Preparations in the" or 000011414.77_00000000 or 000001412.31_APS27666.

    I am interested in strings like this 00002_00005_00002, with these the underscore _ is always in the same spot, index 6 and index 12. So I trying extract the numbers from the characters from the group that starts with the underscore.

    e.g. 00002_00005_00007 becomes

    Group A = 5

    Group B = 7

    I have am using an case to find the positions 6 & 12 then substring the characters I want, then cast as int.

    SELECT CASE E_INA23
           WHEN CHARINDEX('_',E_INA23,6)
           THEN CAST(SUBSTRING(E_INA23,6,11)AS INT)
           END AS 'DIV A',
           CASE E_INA23
           WHEN CHARINDEX('_',E_INA23,12)
           THEN CAST(SUBSTRING(E_INA23,12,17)AS INT)
           END AS 'DIV B'
    FROM xxxx.yyyy

    But I am getting error; conversion failed when converting varchar to data type int. It seems it is trying to convert the other strings like "Preparations in the". Not just the when the underscore is at the index position.

    Thanks



    • Modifié Cameronh vendredi 30 mars 2012 00:32
    vendredi 30 mars 2012 00:28

Réponses

  • I didn't select these fields in CTE. Add them to cte if you need them in the WHERE, e.g.

    ;WITH cte AS 
    
    (SELECT e_ina23, SUBSTRING(e_ina23,7,5) AS [Div A], SUBSTRING(e_ina23,13,LEN(e_ina23)) AS [Div B],
    e_icon_title as 'Activity Type', e_ina13,
    e_ina01, e_ina08, e_ina12, e_ina15
    FROM db.tbl 
    WHERE E_INA23 LIKE '[0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9]'
    )
    
    SELECT [Activity Type],
           e_ina23, 
           CAST([Div A] AS INT) AS [Div A], 
           CAST([Div B] AS INT) AS [Div B], 
           
           case e_ina13 
               
               when 'camillew' then 'Camille Wood'
               when 'ian' then 'Ian Peel'
               when 'michael' then 'Michael Wood'
                 
           end as [Staff Member]
           ,count(*) as 'Activities During Period'
    
    FROM cte
    where  
    e_ina01 = 'EISS' 
    and e_ina13 in ('camillew','ian','michael') 
    and e_ina08 = 'member' 
    and e_ina12 = 'activity' 
    --and e_ina15 between (@startdate) AND (@enddate)
    group by [Activity Type],, e_ina13,e_ina23,
    [Div A],[Div B]


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marqué comme réponse Cameronh mardi 3 avril 2012 00:11
    lundi 2 avril 2012 00:44

Toutes les réponses

  • Hope I got what you are looking for:

    ALTER Function [dbo].[SplitString]	(@List Varchar(Max), @Delimiter Char(1))
    Returns @Items Table (Item Varchar(100))
    As
    Begin
        Declare @Item Varchar(12), @Pos TinyInt
        While Len(@List) > 0
        Begin
            Set @Pos = CharIndex(@Delimiter, @List)
            If @Pos = 0 Set @Pos = Len(@List) + 1
            Set @Item = Left(@List, @Pos - 1)
            
            Insert @Items 
            Select Ltrim(Rtrim(@Item))
            
            Set @List = SubString(@List, @Pos + Len(@Delimiter), Len(@List))
        End
        Return
    End
    
    Go
    
    Declare @myTable Table (E_INA23 Varchar(254))
    Insert Into @myTable 
    Select 'Preparations in the' Union All 
    Select '00011414.77_00000000' Union All 
    Select '000001412.31_APS27666' Union All 
    Select '00002_00005_00007'
    
    ;With CTE 
    As
    (
    	Select * 
    	From @myTable As MainQry 
    		Cross Apply (Select * From dbo.SplitString(MainQry.E_INA23, '_')) As Sub
    	Where 
    		IsNumeric(Sub.Item) = 1 
    )
    ,CTE1 
    As
    (
    	Select 
    		E_INA23 
    	From 
    		CTE 
    	Group By 
    		E_INA23 
    	Having Count(*) = 3 
    )
    Select 
    	CTE1.E_INA23 
    	,Convert(Int, CTE.Item) As Item 
    From 
    	CTE1 
    	Inner Join CTE On CTE1.E_INA23 = CTE.E_INA23 
    
    --output
    E_INA23	Item
    00002_00005_00007	2
    00002_00005_00007	5
    00002_00005_00007	7



    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    vendredi 30 mars 2012 00:40
  • One more way:

    Declare @myTable Table (E_INA23 Varchar(254))
    Insert Into @myTable 
    Select 'Preparations in the' Union All 
    Select '00011414.77_00000000' Union All 
    Select '000001412.31_APS27666' Union All 
    Select '00002_00005_00007'
    
    Select 
    	Convert(Int, SubString(E_INA23, 1, 5)) As ID1 
    	,Convert(Int, SubString(E_INA23, 7, 5)) As ID2
    	,Convert(Int, SubString(E_INA23, 13, 5)) As ID3
    From @myTable 
    Where PatIndex('[0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9]', E_INA23) = 1 
    
    --output
    ID1	ID2	ID3
    2	5	7


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Proposé comme réponse vinaypugalia vendredi 30 mars 2012 04:35
    vendredi 30 mars 2012 00:46
  • Try:

    DECLARE @myTable TABLE (E_INA23 VARCHAR(254))
    INSERT INTO @myTable 
    SELECT 'Preparations in the' UNION ALL 
    SELECT '00011414.77_00000000' UNION ALL 
    SELECT '000001412.31_APS27666' UNION ALL 
    SELECT '00002_00005_00007'
    
    ;WITH cte AS (SELECT e_ina23, SUBSTRING(e_ina23,7,5) AS [Div A], SUBSTRING(e_ina23,13,LEN(e_ina23)) AS [Div B]
    
    FROM @myTable 
    WHERE E_INA23 LIKE '[0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9]'
    )
    
    SELECT e_ina23, CAST([Div A] AS INT) AS [Div A], CAST([Div B] AS INT) AS [Div B] FROM cte 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    vendredi 30 mars 2012 00:50
  • Hi,

    This is a better option to go with Regular expression.

    vendredi 30 mars 2012 02:26
  • Thanks for replies Arbi, why the =1 after the PatIndex?
    • Modifié Cameronh vendredi 30 mars 2012 03:03
    vendredi 30 mars 2012 03:03
  • I have included your cte in my statement but it seems to not be grouping by the e_icon_title anymore?

    DECLARE @myTable TABLE (E_INA23 VARCHAR(254))
     
    Insert Into @myTable
    select E_INA23
    from db.tbl

    ;WITH cte AS

    (SELECT e_ina23, SUBSTRING(e_ina23,7,5) AS [Div A], SUBSTRING(e_ina23,13,LEN(e_ina23)) AS [Div B]

    FROM @myTable
    WHERE E_INA23 LIKE '[0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9]'
    )

    SELECT e_icon_title as 'Activity Type',
           db.tbl.e_ina23,
           CAST([Div A] AS INT) AS [Div A],
           CAST([Div B] AS INT) AS [Div B],
           
           case e_ina13
               
               when 'camillew' then 'Camille Wood'
               when 'ian' then 'Ian Peel'
               when 'michael' then 'Michael Wood'
                 
           end as [Staff Member]
           ,count(*) as 'Activities During Period'

    FROM cte, db.tbl
    where  
    e_ina01 = 'EISS'
    and e_ina13 in ('camillew','ian','michael')
    and e_ina08 = 'member'
    and e_ina12 = 'activity'
    --and e_ina15 between (@startdate) AND (@enddate)
    group by e_icon_title, e_ina13,db.tbl.e_ina23,[Div A],[Div B]



    • Modifié Cameronh vendredi 30 mars 2012 04:50
    vendredi 30 mars 2012 04:21
  • Thanks for replies Arbi, why the =1 after the PatIndex?

    Because you want to get any E_INA23 that matches with your pattern XXXXX_XXXXX_XXXXX. 

    Besides, if you want Naomi's solution or mine, NO need to use Local Table Variables. With that to simulate your data. 


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    vendredi 30 mars 2012 05:19
  • "Besides, if you want Naomi's solution or mine, NO need to use Local Table Variables. With that to simulate your data. " Sorry you are now saying I don't need a local table?

    But this not returning any results.

    Drop Table #myTable
    Create table #myTable (E_INA23 Varchar(254))

    Insert Into #myTable (E_INA23)
    select E_INA23
    from db.tbl

    select e_icon_title as 'Activity Type',

           case e_ina23
               
               when 'camillew' then 'Camille Wood'
               when 'ian' then 'Ian Peel'
               when 'michael' then 'Michael Wood'
                 
           end as [Staff Member]
           ,count(*) as 'Activities During Period'       
           ,Convert(Int, SubString(E_INA23, 7, 5)) As [ID2]
           ,Convert(Int, SubString(E_INA23, 13, 5)) As [ID3]

    from db.tbl

    where  
    e_ina01 = 'EISS'
    and e_ina13 in ('camillew','ian','michael')
    and e_ina08 = 'member'
    and e_ina12 = 'activity'
    --and e_ina15 between (@startdate) AND (@enddate)
    and PatIndex('[0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9]',E_INA23) = 1

    group by e_icon_title, E_INA23

    vendredi 30 mars 2012 05:34
  • If you comment PatIndex line 

    and PatIndex('[0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9]',E_INA23) = 1

    Do you see any resultset in your Statements output?


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    vendredi 30 mars 2012 05:39
  • yeah i do, the group by is grouping by the title again. but the staff member and ID2 & Id3 are null.
    • Modifié Cameronh vendredi 30 mars 2012 05:44
    vendredi 30 mars 2012 05:43
  • If you run following SQL statement, could you please provide some output for us:

    
    
    select e_icon_title as 'Activity Type', 
    
           case e_ina23 
               
               when 'camillew' then 'Camille Wood'
               when 'ian' then 'Ian Peel'
               when 'michael' then 'Michael Wood'
                 
           end as [Staff Member]
           ,count(*) as 'Activities During Period'       
           ,SubString(E_INA23, 7, 5) As [ID2]
           ,SubString(E_INA23, 13, 5) As [ID3]
    
    from dbo.tbl
    
    where  
    e_ina01 = 'EISS' 
    and e_ina13 in ('camillew','ian','michael') 
    and e_ina08 = 'member' 
    and e_ina12 = 'activity' 
    --and e_ina15 between (@startdate) AND (@enddate)
    --and PatIndex('[0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9]',E_INA23) = 1
    
    group by e_icon_title, E_INA23



    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.


    vendredi 30 mars 2012 05:46
  • Try:

    ;WITH cte AS 
    
    (SELECT e_ina23, SUBSTRING(e_ina23,7,5) AS [Div A], SUBSTRING(e_ina23,13,LEN(e_ina23)) AS [Div B],
    e_icon_title as 'Activity Type', e_ina13
    
    FROM db.tbl 
    WHERE E_INA23 LIKE '[0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9]'
    )
    
    SELECT [Activity Type],
           e_ina23, 
           CAST([Div A] AS INT) AS [Div A], 
           CAST([Div B] AS INT) AS [Div B], 
           
           case e_ina13 
               
               when 'camillew' then 'Camille Wood'
               when 'ian' then 'Ian Peel'
               when 'michael' then 'Michael Wood'
                 
           end as [Staff Member]
           ,count(*) as 'Activities During Period'
    
    FROM cte
    where  
    e_ina01 = 'EISS' 
    and e_ina13 in ('camillew','ian','michael') 
    and e_ina08 = 'member' 
    and e_ina12 = 'activity' 
    --and e_ina15 between (@startdate) AND (@enddate)
    group by [Activity Type],, e_ina13,e_ina23,
    [Div A],[Div B]
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    vendredi 30 mars 2012 18:53
  • Arbi's code:

    Activity Type, Staff Member, Activites, ID2, ID3,

    Activity-aaaa, null,1, null,null

    Acitvity-bbbb,null,4, null, null

    Activity-ccccc, null,1976, null, null

    yeah i do, the group by is grouping by the title again. but the staff member and ID2 & Id3 are null.


    • Modifié Cameronh lundi 2 avril 2012 00:20
    lundi 2 avril 2012 00:10
  • Naomi's Code:

    Msg 207, Level 16, State 1, Line 26
    Invalid column name 'e_ina01'.
    Msg 207, Level 16, State 1, Line 28
    Invalid column name 'e_ina08'.
    Msg 207, Level 16, State 1, Line 29
    Invalid column name 'e_ina12'.

    Looks like you can't reference the table declared in the cte in the where which is outside the cte.


    • Modifié Cameronh lundi 2 avril 2012 00:20
    lundi 2 avril 2012 00:12
  • ;WITH cte  AS

    (SELECT e_ina23, SUBSTRING(e_ina23,7,5) AS [Div A], SUBSTRING(e_ina23,13,LEN(e_ina23)) AS [Div B],
    e_icon_title as 'Activity Type', e_ina13,e_ina08,e_ina12,e_ina01

    FROM db.tbl
    WHERE E_INA23 LIKE '[0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9]'
    )

    SELECT [Activity Type],
           e_ina23,
           CAST([Div A] AS INT) AS [Div A],
           CAST([Div B] AS INT) AS [Div B],

           case e_ina13

               when 'camillew' then 'Camille Wood'
               when 'ian' then 'Ian Peel'
               when 'michael' then 'Michael Wood'

           end as [Staff Member]
           ,count(*) as 'Activities During Period'

    FROM cte
    where  
    e_ina01 = 'EISS'
    and e_ina13 in ('camillew','ian','michael')
    and e_ina08 = 'member'
    and e_ina12 = 'activity'
    --and e_ina15 between (@startdate) AND (@enddate)
    group by [Activity Type], e_ina13,e_ina23,
    [Div A],[Div B]

    returns no data.

                                   
    lundi 2 avril 2012 00:42
  • I didn't select these fields in CTE. Add them to cte if you need them in the WHERE, e.g.

    ;WITH cte AS 
    
    (SELECT e_ina23, SUBSTRING(e_ina23,7,5) AS [Div A], SUBSTRING(e_ina23,13,LEN(e_ina23)) AS [Div B],
    e_icon_title as 'Activity Type', e_ina13,
    e_ina01, e_ina08, e_ina12, e_ina15
    FROM db.tbl 
    WHERE E_INA23 LIKE '[0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9]'
    )
    
    SELECT [Activity Type],
           e_ina23, 
           CAST([Div A] AS INT) AS [Div A], 
           CAST([Div B] AS INT) AS [Div B], 
           
           case e_ina13 
               
               when 'camillew' then 'Camille Wood'
               when 'ian' then 'Ian Peel'
               when 'michael' then 'Michael Wood'
                 
           end as [Staff Member]
           ,count(*) as 'Activities During Period'
    
    FROM cte
    where  
    e_ina01 = 'EISS' 
    and e_ina13 in ('camillew','ian','michael') 
    and e_ina08 = 'member' 
    and e_ina12 = 'activity' 
    --and e_ina15 between (@startdate) AND (@enddate)
    group by [Activity Type],, e_ina13,e_ina23,
    [Div A],[Div B]


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marqué comme réponse Cameronh mardi 3 avril 2012 00:11
    lundi 2 avril 2012 00:44
  • yeah i did that in the code above, but it returns no data.????
    lundi 2 avril 2012 03:27
  • Ok, it means that there are no rows matching your criteria and pattern.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    lundi 2 avril 2012 03:43
  • this works but the group by isn't group by activity type, staff member and count;



    DECLARE @myTable TABLE (E_INA23 VARCHAR(254))
     
    Insert Into @myTable
    select E_INA23
    from db.tbl

    ;WITH cte AS

    (SELECT e_ina23, SUBSTRING(e_ina23,7,5) AS [Div A], SUBSTRING(e_ina23,13,LEN(e_ina23)) AS [Div B]

    FROM @myTable
    WHERE E_INA23 LIKE '[0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9]'
    )

    SELECT e_icon_title as 'Activity Type',
           
           CAST([Div A] AS INT) AS [Div A],
           CAST([Div B] AS INT) AS [Div B],
           
           case e_ina13
               
               when 'camillew' then 'Camille Wood'
               when 'ian' then 'Ian Peel'
               when 'michael' then 'Michael Wood'
                 
           end as [Staff Member]
           ,count(*) as 'Activities During Period'

    FROM cte, db.tbl
    where  
    e_ina01 = 'EISS'
    and e_ina13 in ('camillew','ian','michael')
    and e_ina08 = 'member'
    and e_ina12 = 'activity'

    --and e_ina15 between (@startdate) AND (@enddate)
    group by e_icon_title, e_ina13,[Div A],[Div B], e_ina08


    results:

    activity type, div a, div b, staff, activities

    acitvity aaaaa, 0, 1, camile wood, 6279

    activity aaaaa, 0, 2, camile wood, 1151

    activity aaaaa, 0, 3, camile wood, 325

    activity aaaaa, 0, 4, camile wood, 88

    activity aaaaa, 0, 5, camile wood, 56


    activity bbbbb , 0, 1, michael wood, 6279

    activity bbbbb , 0, 2, michael wood, 1151

    activity bbbbb , 0, 3, michael wood, 325

    lundi 2 avril 2012 05:33
  • There is no JOIN condition between CTE and your table, so your result will be a cross join. What I posted above is the correct way and if you're not getting rows, it means that there are NO ROWS matching all conditions. You may try removing conditions one by one to find out which condition eliminates all rows.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    lundi 2 avril 2012 14:25
  • select e_ina13, e_ina08, e_ina12
    from db.tbl
    where e_ina13 in ('camillew','ian','michael') and e_ina08 = 'member' and e_ina12 = 'activity' and e_ina01 = 'EISS'
    group by e_ina13, e_ina08, e_ina12

    returns the correct data;

    Camilew, Member, Activity

    Ian, Member, Activity

    Michael, Member, Activity

    So the where is fine, it must be something else.

    lundi 2 avril 2012 22:23
  • Well, don't forget that we have an extra condition to only select 99999_88888_99999 type of fields (5 digits then _ then 5 digits then _ then 5 digits). Try:

    select e_ina13, e_ina08, e_ina12 from db.tbl where e_ina13 in ('camillew','ian','michael') and e_ina08 = 'member' and e_ina12 = 'activity' and e_ina01 = 'EISS'

    AND E_INA23 LIKE '[0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9]' group by e_ina13, e_ina08, e_ina12

    I added back the condition for E_INA23. If you get 0 rows back, it means your pattern is something else, not 5 digits, _, 5 digits, _, 5 digits.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    lundi 2 avril 2012 22:31
  • Wow I love it when the client doesn't provide enough information.wasted 2 days trying to figure out what was wrong with the code and there's nothing.

    When e_ina08 = 'member' they don't have any e_ina23 which is why this was returning null, but they didn't tell me. They orginaly said they should be a match.

    But there only is when e_ina08 = 'employer' then I get results.

    Can't thank you enough Naomi, been very frustrating for me.

    mardi 3 avril 2012 00:10