none
select a string from a column RRS feed

  • Question

  • from the below output from sql i need to get a string LDERRI only how do i do it ?

    'A0=1006;amount:40175=2027.58;A5=3004;A32=LDERRI;BF=PA997;A1=U1;GN=LDERRI;A22=200541'

    Thursday, January 2, 2020 11:45 AM

All replies

  • i need to select LDERRI only 
    Thursday, January 2, 2020 11:46 AM
  • Check if STRING_SPLIT is available in your SQL:

     

    declare @example as varchar(MAX) = 'A0=1006;amount:40175=2027.58;A5=3004;A32=LDERRI;BF=PA997;A1=U1;GN=LDERRI;A22=200541'
     
    select TOP(1) SUBSTRING([value], LEN('A32=') + 1, LEN(@example)) from STRING_SPLIT(@example, ';') where [value] like 'A32=' + '%'





    • Edited by Viorel_MVP Thursday, January 2, 2020 12:02 PM
    Thursday, January 2, 2020 11:58 AM
  • Is this what you need?

    declare @str varchar(max) = 'A0=1006;amount:40175=2027.58;A5=3004;A32=LDERRI;BF=PA997;A1=U1;GN=LDERRI;A22=200541'
    
    select substring(@str, charindex('A32=', @str) + 4, charindex(';', @str, charindex('A32=', @str)) - charindex('A32=', @str) - 4)
    Thursday, January 2, 2020 12:09 PM
  • What exactly do you mean by "i need to get a string LDERRI"?  Do you mean the value after A32= and GN=?

    Thursday, January 2, 2020 1:20 PM
    Moderator
  • declare @s varchar(2000)='A0=1006;amount:40175=2027.58;A5=3004;A32=LDERRI;BF=PA997;A1=U1;GN=LDERRI;A22=200541'
    
    select Replace(value,'A32=','') value
    from string_split(@s,';')
    Where value like'A32=%' 

    Thursday, January 2, 2020 2:35 PM
    Moderator
  • DECLARE @s varchar(1000);
    SET @s = 'A0=1006;amount:40175=2027.58;A5=3004;A32=LDERRI;BF=PA997;A1=U1;GN=LDERRI;A22=200541';
    SELECT SUBSTRING(@s, CHARINDEX('A32=', @s) + LEN('A32='), CHARINDEX(';', SUBSTRING(@s, CHARINDEX('A32=', @s) + LEN('A32='), LEN(@s))) - 1);


    A Fan of SSIS, SSRS and SSAS

    Thursday, January 2, 2020 2:52 PM
  • Hi veer786,

    May I know the string LDERRI is pointing to 'A32=' or 'GN=' ? 

    --including two scenarios
    
    declare @record varchar(2000)='A0=1006;amount:40175=2027.58;A5=3004;A32=LDERRI;BF=PA997;A1=U1;GN=LDERRI;A22=200541'
    
    select substring(value,charindex('=',value)+1,1000) value
    from string_split(@record,';')
    where value like 'A32=%' or value like 'GN='

    Best Regards,

    Llily


    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


    • Edited by Lily Lii Friday, January 3, 2020 2:24 AM
    • Proposed as answer by Lily Lii Monday, January 6, 2020 2:47 AM
    Friday, January 3, 2020 2:23 AM
  • Hi veer786,

    Do the answers above help you? It's so kind of you to mark helpful replies as answers in order to close this thread. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,

    Lily


    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

    Monday, January 6, 2020 2:48 AM