replacement of InStr function in SQL

Question

Post reply

replacement of InStr function in SQL

nabajyoti.b

SSCrazy

Points: 2177
More actions
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

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

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

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

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

nabajyoti.b SSCrazy Points: 2177 More actions · Answer 2

nabajyoti.b

SSCrazy

Points: 2177

March 10, 2009 at 4:33 am

#956663

Many Thanks .....

Apreciate Much 🙂

John Dempsey Hall of Fame Points: 3225 More actions · Answer 3

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

Stad SSC Eights! Points: 964 More actions · Answer 4

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

John Dempsey Hall of Fame Points: 3225 More actions · Answer 5

FINDSTRING

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

cait5801 Newbie Points: 1 More actions · Answer 6

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"