Post reply

How to filter out the string via CHARINDEX?

  • February 27, 2011 at 11:15 pm

    #235238

    Hi,

    Is there anybody help me take a look at the CHARINDEX operation below, I want to filter out the string between "FROM" and the a. I did some tries and we can use the following string to filter out, but how can I do if the @sqlquery = 'select a.* from AWD.dob.Account', I don't which condition can help me filter out the string "AWD.dob.Account", do you have any solution to resolve it? really appreciated if you can share anything with me. Thanks a lot.

    THanks

    Lindsay

    Declare @sqlquery VARCHAR(max)

    Set @sqlquery = 'select a.* from AWD.dbo.Account a'

    SELECT SubString(@sqlQuery,PATINDEX('%from %',@sqlQuery)+5,CHARINDEX(' ',@sqlQuery,PATINDEX('%from %',@sqlQuery)+5)-PATINDEX('%from %',@sqlQuery)-5)

  • February 27, 2011 at 11:31 pm

    #1292161

    Use the below SQL statment to get the request

    Declare @sqlquery VARCHAR(max)

    SET @sqlquery = 'select a.* from AWD.dbo.Account a'

    SELECT substring(@sqlQuery, charindex('AWD.dbo.Account', @sqlquery), len('AWD.dbo.Account'))

  • February 28, 2011 at 10:58 am

    #1292456

    Looks like the answer you got will return the same thing you already figured out (although maybe a little simpler). What is the result you are looking for?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 28, 2011 at 2:18 pm

    #1292591

    I think the issue is, They need to parse out table name even if it is not aliased. If that is not the case please provide expected results.

    Declare @sqlquery VARCHAR(max)

    Set @sqlquery = 'select a.* from AWD.dbo.Account'

    select SUBSTRING(@sqlquery, charindex('from',@sqlquery)+5,len(substring(REVERSE(@sqlquery),1,charindex('morf',REVERSE(@sqlquery))-2)))

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • February 28, 2011 at 7:44 pm

    #1292685

    Thanks for your reply all^_^

    Basically, I only want to filter out the table info after in one query, but the format after "From" is different, sometimes, it it has only table info, somethings, it shown as [tableinfo] [enter] [where info], I hope I can have a SP only filter out the table info. Now I have found the solution, we only use space to replace Char(10) and Char(13), and then use space as key word to filter out the table info. Thanks again.

    Thanks

    Ling

Viewing 5 posts - 1 through 4 (of 4 total)

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