locked
Eliminate characters RRS feed

  • Question

  • User1519602350 posted

    Sql Query

    Select content from pagecontent where content LIKE '%well%' and contentid=153
    

    Table Structure

    Content                                                                             contentid

    Lorem ipsum dolor sit amet wellness healthcare                       153

    Requirement

    While executing query I am looking for the result like starting from wellness healthcare...... not from "lorem ipsum dolor...." So basically i want to eliminate all previous characters/words. So it will start from matching characters.

    Kindly help me out with this.

    Thanks

    Thursday, July 25, 2013 4:56 PM

Answers

  • User-1005100348 posted

    If you are using a version of SQL Server prior to 2005 try this:

    Select SUBSTRING(content, CHARINDEX('well', content), LEN(content) - CHARINDEX('well', content) + 1)

    Hope this helps. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 25, 2013 5:26 PM
  • User-1005100348 posted

    Try to replace the CharIndex by PatIndex.

     

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 25, 2013 7:17 PM

All replies

  • User-1005100348 posted

    Try something like this:

    Select RIGHT(content, LEN(content) - CHARINDEX('well', content) + 1) 
    from pagecontent 
    where content LIKE '%well%' and contentid=153

    Hope this helps.

    Thursday, July 25, 2013 5:07 PM
  • User1519602350 posted

    Try something like this:
    Select RIGHT(content, LEN(content) - CHARINDEX('well', content) + 1)
    from pagecontent
    where content LIKE '%well%' and contentid=153

    Tried with query i am getting an error : There was an error parsing the query. [ Token line number = 1,Token line offset = 8,Token in error = RIGHT ]

    Thursday, July 25, 2013 5:10 PM
  • User-1005100348 posted

    If you are using a version of SQL Server prior to 2005 try this:

    Select SUBSTRING(content, CHARINDEX('well', content), LEN(content) - CHARINDEX('well', content) + 1)

    Hope this helps. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 25, 2013 5:26 PM
  • User1519602350 posted

    i am unable to get: The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = LEN ]

    Thursday, July 25, 2013 5:30 PM
  • User1519602350 posted

    Thank you so much spapim.

    Final query

    Select SUBSTRING(content, CHARINDEX('lorem', content), LEN(CAST(Content as nvarchar(1000))) - CHARINDEX('well', content) + 1) from pagecontent
    where content LIKE '%well%' and contentid=153
    Thursday, July 25, 2013 5:36 PM
  • User1519602350 posted

    This query is for search website. Here CHARINDEX @0 is not taking entered value

    var sqlSelect = "Select a.maxcontentid,a.url,a.Title, SUBSTRING(b.content, CHARINDEX(@0, b.content), 
    LEN(CAST(b.content as nvarchar(1000))) - CHARINDEX(@0, b.content) + 1) as Content
    FROM (SELECT Pages.Title as Title, Pages.Url, PageContent.PageId, MAX(PageContent.ContentId) as maxcontentid,
    MAX(PageContent.Published) as maxdate FROM Pages INNER JOIN PageContent on PageContent.PageId=Pages.PageId
    LEFT JOIN InnerPageSliderContent ON Pages.Url= InnerPageSliderContent.PageUrl WHERE PageContent.Content LIKE @0
    AND PageContent.contentid IN (select max(PageContent.contentid) from pagecontent
    where Pages.pageid=PageContent.pageid and contentid=153) group by Pages.PageId,Pages.Title,Pages.Url,PageContent.PageId) a
    INNER JOIN pagecontent b ON a.Pageid = b.Pageid WHERE b.ContentId =a.maxcontentid and b.Published < getdate()
    ORDER BY maxcontentid DESC"
    ;
    Thursday, July 25, 2013 5:58 PM
  • User-1005100348 posted

    Try to replace the CharIndex by PatIndex.

     

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 25, 2013 7:17 PM