Post reply

replacement of InStr function in SQL

  • March 10, 2009 at 3:53 am

    #129680

    how do we change

    IIf(InStr([tblA].col1,"abc")>0,[tblA].col1,"") AS xyz

    into a sql

  • March 10, 2009 at 4:15 am

    #956651

    I don't remember IIF strings too well but I think it would be something like the following;

    select

    case

    when col1 like '%abc%' then col1

    else ''

    end as xyz

    from Table1

    Links to help:

    http://www.upsizing.co.uk/Art8_ConvertingQueriesToViews2.aspx

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62633

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • March 10, 2009 at 4:33 am

    #956663

    Many Thanks .....

    Apreciate Much 🙂

  • March 11, 2009 at 8:52 am

    #957559

    Another way to do it is using a function like InStr called CHARINDEX in SQL Server as follows:

    SELECT CASE

    WHEN CHARINDEX('abc', [tblA].col1) > 0 THEN [tblA].col1

    ELSE ''

    END

    FROM tblA

  • July 5, 2011 at 2:52 pm

    #1348597

    Hi ,

    I know this is not a question to ask in this section.But I have to do the same in SSIS.

    Please add suggestion to get it done in SSIS.

    Thanks!

    Stad

  • July 5, 2011 at 3:08 pm

    #1348605

    FINDSTRING

    http://msdn.microsoft.com/en-us/library/ms141748.aspx

  • July 23, 2021 at 4:54 pm

    #3913245

    would anyone on this post know how to replace instra in this

    select substr(caption,(instr(caption,'@') + 1 ), length(caption)), (instr(caption,'@') + 1)

    FROM "CONVIVA_ANALYTICS"."INSTAGRAM_POST_METRICS"

    WHERE postid = '1971324349038518078_1547775030'

    ORDER BY caption;

    I am trying to grab all of the words after the @ symbol in a column like this:

    So I would expect to see - revjahwar ,nfl,nflnetwork

    "I Will have a shoe & sock line for u soon and great price points that fit your budget. #Truth @revjahwar #51&Done #21reasons #21dayswithPrime #IminmyPurpose #Purpose=Peace #iBelieve #Tiredofplayinggames #2019AintNobodyCare @nfl @nflnetwork"

Viewing 7 posts - 1 through 6 (of 6 total)

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