none
split string using delimiter and only keep the last part of the string

    질문

  • Hello,

    I have a string like this 

    Company A, LTD. 05.670%;  Company B INC. 25.170%;  Company C INC. 69.160%

    I want the result to be the majority owner which is the last part:

    Company C INC. 69.160%

    Could you please advise how to write the sql query?

    Thanks!

    Sarah


    Sarah Wang

    2018년 7월 12일 목요일 오후 11:01

답변

  • Thank for your consideration! The majority owner might be in the middle in some cases.

    Your query works great for the example given above.

    However, the following strings also occur in the table, some with one owner, some with two owners, some do not have ; to separate the owners. Could you please help with the different scenarios?

    Thanks!!

    ID COMPANY
    1       CAM CORPORATION: 100.000%
    2       FOR CORP.: 40.000%;  RIO CORPORATION: 60.000%
    4      DENI CORP.: 100.000%
    7      GOLDEN INC.: 100.000%
    13      ISO LTD.: 100.000%
    19      WESCA INC.: 100.000%
    22      JC CO., LTD.: 10.000%  CAM   CORPORATION: 30.000%  DENI INC.:   60.000%


    Sarah Wang

    • 답변으로 표시됨 SXQ 2018년 7월 25일 수요일 오후 9:37
    2018년 7월 13일 금요일 오후 10:19

모든 응답

  • Hello,

    I have a string like this 

    Company A, LTD. 05.670%;  Company B INC. 25.170%;  Company C INC. 69.160%

    I want the result to be the majority owner which is the last part:

    Company C INC. 69.160%

    Could you please advise how to write the sql query?

    Thanks!

    Sarah


    Sarah Wang

    You could do similar to this:

    DECLARE @s VARCHAR(255) = 'Company A, LTD. 05.670%;  Company B INC. 25.170%;  Company C INC. 69.160%'
    SELECT REVERSE(LEFT(REVERSE(@s), CHARINDEX(';', REVERSE(@s))-2))

    2018년 7월 12일 목요일 오후 11:28
  • Hi Sarah,

    What's your SQL Server version?

    To achieve your requirement, you can try this:

    DECLARE @s VARCHAR(255) = 'Company A, LTD. 05.670%;  Company B INC. 25.170%;  Company C INC. 69.160%'
    
    SELECT LTRIM(RIGHT(@s,CHARINDEX(';',REVERSE(@s))-1))
    

    And if you are using SQL Server 2016 or higher. You can try STRING_SPLIT() function to split the string based on ';' separator. Like:

    DECLARE @s VARCHAR(255) = 'Company A, LTD. 05.670%;  Company B INC. 25.170%;  Company C INC. 69.160%'
    
    --SELECT LTRIM(RIGHT(@s,CHARINDEX(';',REVERSE(@s))-1))
    
    SELECT value
    FROM (
    	SELECT LTRIM(value) AS value,ROW_NUMBER() OVER(ORDER BY CHARINDEX(value,@s) DESC) as rn
    	FROM string_split(@s,';') ) t
    WHERE t.rn = 1

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 7월 13일 금요일 오전 6:21
  • As long as the string doesnt contain any characters like <,> etc you can use this also

    declare @t table
    (
    x varchar(max)
    )
    
    insert @t values('Company A, LTD. 05.670%;  Company B INC. 25.170%;  Company C INC. 69.160%')
    
    SELECT RTRIM(LTRIM(CAST('<Root><Node>' + REPLACE(x,';','</Node><Node>') + '</Node></Root>' AS xml).query('/Root/Node[last()]').value('.','varchar(100)'))) AS YourValue
    FROM @t t
    
    
    /*
    Output
    ----------------------------------------------------------
    YourValue
    ----------------------------------------------------------
    Company C INC. 69.160%
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 7월 13일 금요일 오전 6:46
  • If the majority owner can be at any position(not the last) in the string, the you can use this:

    declare @str varchar(100) = 'Company A, LTD. 05.670%;  Company B INC. 25.170%;  Company C INC. 69.160%'
    
    ;with cte as(
    SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) String, cast(LTRIM(RTRIM(substring(Split.a.value('.', 'VARCHAR(100)'), charindex('.', Split.a.value('.', 'VARCHAR(100)'))+1, charindex('%', Split.a.value('.', 'VARCHAR(100)')) - charindex('.', Split.a.value('.', 'VARCHAR(100)'),1) - 1))) AS numeric(10,5)) Value
    FROM  (SELECT CAST ('<M>' + REPLACE(@str, ';', '</M><M>') + '</M>' AS XML) AS String  
         ) AS A CROSS APPLY String.nodes ('/M') AS Split(a)
    )
    select String from cte a join (select max(Value) as Value from cte) b on a.Value = b.Value
    2018년 7월 13일 금요일 오후 1:37
  • Thank for your consideration! The majority owner might be in the middle in some cases.

    Your query works great for the example given above.

    However, the following strings also occur in the table, some with one owner, some with two owners, some do not have ; to separate the owners. Could you please help with the different scenarios?

    Thanks!!

    ID COMPANY
    1       CAM CORPORATION: 100.000%
    2       FOR CORP.: 40.000%;  RIO CORPORATION: 60.000%
    4      DENI CORP.: 100.000%
    7      GOLDEN INC.: 100.000%
    13      ISO LTD.: 100.000%
    19      WESCA INC.: 100.000%
    22      JC CO., LTD.: 10.000%  CAM   CORPORATION: 30.000%  DENI INC.:   60.000%


    Sarah Wang

    • 답변으로 표시됨 SXQ 2018년 7월 25일 수요일 오후 9:37
    2018년 7월 13일 금요일 오후 10:19
  • Hi,

    What is your sql server version?

    2018년 7월 14일 토요일 오전 4:26
  • Thank for your consideration! The majority owner might be in the middle in some cases.

    Your query works great for the example given above.

    However, the following strings also occur in the table, some with one owner, some with two owners, some do not have ; to separate the owners. Could you please help with the different scenarios?

    Thanks!!

    ID COMPANY
    1       CAM CORPORATION: 100.000%
    2       FOR CORP.: 40.000%;  RIO CORPORATION: 60.000%
    4      DENI CORP.: 100.000%
    7      GOLDEN INC.: 100.000%
    13      ISO LTD.: 100.000%
    19      WESCA INC.: 100.000%
    22      JC CO., LTD.: 10.000%  CAM   CORPORATION: 30.000%  DENI INC.:   60.000%


    Sarah Wang

    All scenarios work fine in my suggestion

    Did you try it at all?

    see illustration below

    declare @t table
    (
    x varchar(max)
    )
    
    insert @t values('Company A, LTD. 05.670%;  Company B INC. 25.170%;  Company C INC. 69.160%')
    insert @t values('CAM CORPORATION: 100.000%')
    insert @t values('FOR CORP.: 40.000%;  RIO CORPORATION: 60.000%')
    insert @t values('DENI CORP.: 100.000%')
    insert @t values('GOLDEN INC.: 100.000%')
    insert @t values('ISO LTD.: 100.000%')
    insert @t values('WESCA INC.: 100.000%')
    insert @t values('JC CO., LTD.: 10.000%  CAM   CORPORATION: 30.000%  DENI INC.:   60.000%')
    
    SELECT RTRIM(LTRIM(CAST('<Root><Node>' + REPLACE(x,';','</Node><Node>') + '</Node></Root>' AS xml).query('/Root/Node[last()]').value('.','varchar(100)'))) AS YourValue
    FROM @t t
    
    
    /*
    Output
    ---------------------------------------------------------
    YourValue
    ----------------------------------------------------
    Company C INC. 69.160%
    CAM CORPORATION: 100.000%
    RIO CORPORATION: 60.000%
    DENI CORP.: 100.000%
    GOLDEN INC.: 100.000%
    ISO LTD.: 100.000%
    WESCA INC.: 100.000%
    JC CO., LTD.: 10.000%  CAM   CORPORATION: 30.000%  DENI INC.:   60.000%
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 7월 14일 토요일 오전 6:04