Post reply

SQL remove part of String Question

  • October 20, 2020 at 5:02 pm

    Go to Answer

    #3799345

    Hello All,

    I have a string something like below("inputstring" column) and i want to exclude part of the inputstring column in the below sql and get desired output as mentioned below outputstring column in the case statement.

    There will be only A or R or E characters in the inputstring always or there wont be any characters there will be only numeric values. if there are no characters simply pull numeric value(like 9,10 rows) if there is A or R in the input string pull only string before that letter A or R(like 1,2,4,5,7,8 rows). And if there E only in the string pull string without A or R pull the string with E(like 3,6 rows). And if there is combination of E with A and/or R pull remaning string with E but not from A and/or R(like 1,2,4,5 rows).  If there is no E and having A and/or R pull remaing string before A/and or R(like row 7)

    if there is E in the string it will always in the 6th Position but A and/or R will be always towards the end in that scenario remove until the starting position of A and/or R and get the Outstring.

    I need this in the case statement from SQL.

    IF OBJECT_ID('Tempdb..#Output') IS NOT NULL BEGIN DROP TABLE #Output END

    CREATE TABLE #Output

    (RowNum int

    ,Inputstring nvarchar(20)

    ,Outputstring nvarchar(20))

    INSERT INTO #Output(RowNum,Inputstring,Outputstring)

    values (1,'11111E0001A1','11111E0001')

    ,(2,'11111E001R1','11111E0001')

    ,(3,'11111E001','11111E0001')

    ,(4,'99999E0002A1R1','99999E002')

    ,(5,'99999E002R2','99999E002')

    ,(6,'99999E002','99999E002')

    ,(7,'7770002A1R1','7770002')

    ,(8,'7770002R2','7770002')

    ,(9,'7770002','7770002')

    ,(10,'222222','222222')

    Select * from #Output order by 1

    Thank you all in the advance and let me know if my explanation is not clear.

  • October 20, 2020 at 6:09 pm

    Answer

    #3799374

    Hi,

    Try:

    select 
        RowNum,
        Inputstring,
        case when patindex('%[AR]%', Inputstring) = 0
            then Inputstring
            else left(Inputstring, patindex('%[AR]%', Inputstring) - 1)
        end
    from #Output

    Hope this help

    https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

  • October 20, 2020 at 6:43 pm

    #3799396

    Thanks for your reply, let me verify this.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply