none
Right Transact isnt working

    Question

  • Hi! good afternoon everyone I've got really stuck with this so I will ask the experts about what's happening!

    What I need: 

     I have a column called Business_Area and i want to split in Two separated by the ':'

    Business_Area                              |

    1 Corporate Services:Compliance |

    2 Corporate Services:Compliance |

    -------------------------------------------|

    My problem is:

    When I split the Left Part My code works Perfectly

    When i Split the Right Part It didnt respect the ':'

    Maybe you will think this is a code problem but my troble is 'THIS CODE works fine with a new table created  and i get the right part correctly' ( sounds crazy)

    My code:

    Left part:

     select Left(Business_Area,charindex(':',Business_Area)) as [Left]
     from dbo.Standards

    Show:

    Left                                

    1 Corporate Services:

    2 Corporate Services:

    Exccellent!!!!!! works fine

    ____________________________________________________________________

    Right part:

      select Right(Business_Area,charindex(':',Business_Area)) as [right]
     from dbo.Standards

    right

    1 Services:Compliance

    2 Services:Compliance

    Doesnt work with only this table!!! i dont know what is brong

    _________________________________________

    What i want:

    right

    1 :Compliance

    2 :Compliance

    Thanks all for your time!!!!

    Tuesday, August 27, 2013 6:54 PM

Answers

  • Try:

      select Right(Business_Area,(len(Business_Area) - charindex(':',Business_Area))) as [right]
     from dbo.Standards


    Regards, Matt Bowler MCITP, My blog | SQL Services

    Tuesday, August 27, 2013 11:07 PM
  • The same logic may not work for LEFT and RIGHT  as the position of ':' is not exactly in the middle.

    The 2nd parameter for RIGHT function is no.of characters from the right end of the string literal.

    So, it is giving un expected result.

    Try this,

    declare @Business_Area varchar(100)='Corporate Services:Compliance'
    select Left(@Business_Area,charindex(':',@Business_Area)) as [Left]
    
    select Right(@Business_Area,charindex(':',@Business_Area)) as [right]
    ---modified one 
    select Right(@Business_Area,charindex(':',reverse(@Business_Area))) as [right]

    There are many other ways using STUFF,SUBSTRING.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Wednesday, August 28, 2013 1:41 AM

All replies

  • If i try to split

    abc:dfg ----------> Works FINE i get :dfg

    abc:Co ---------------> Doesnt work with Co i dont know why i get

    c:Co in the right part instead of :Co

    Tuesday, August 27, 2013 7:06 PM
  • Try:

      select Right(Business_Area,(len(Business_Area) - charindex(':',Business_Area))) as [right]
     from dbo.Standards


    Regards, Matt Bowler MCITP, My blog | SQL Services

    Tuesday, August 27, 2013 11:07 PM
  • Or if you still want the colon at the front:

     select Right(Business_Area,(len(Business_Area) - charindex(':',Business_Area))+1) as [right]
     from dbo.Standards


    Regards, Matt Bowler MCITP, My blog | SQL Services

    Tuesday, August 27, 2013 11:13 PM
  • The same logic may not work for LEFT and RIGHT  as the position of ':' is not exactly in the middle.

    The 2nd parameter for RIGHT function is no.of characters from the right end of the string literal.

    So, it is giving un expected result.

    Try this,

    declare @Business_Area varchar(100)='Corporate Services:Compliance'
    select Left(@Business_Area,charindex(':',@Business_Area)) as [Left]
    
    select Right(@Business_Area,charindex(':',@Business_Area)) as [right]
    ---modified one 
    select Right(@Business_Area,charindex(':',reverse(@Business_Area))) as [right]

    There are many other ways using STUFF,SUBSTRING.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Wednesday, August 28, 2013 1:41 AM
  • Thanks a lot!! both answers works fine!!! thanks you a lot! Sarat and matt!!!
    Wednesday, August 28, 2013 4:32 PM