Post reply

CHARINDEX manipulation

  • September 13, 2010 at 4:05 am

    #141925

    Hi there,

    I am trying to get Emergency information from one of my sql server 2005 tables.

    Here are the 2 comments i am testing, there are 1000's more!!

    Please deal as an emergency.

    and

    The door is not secure.

    But these could change as they are free text "The window is not secure." or "Please deal as a emergency."

    What i am trying to do is get the whether there is an occurrence of the phrase 'please deal as an emergency' or 'not secure'.

    But the CHARINDEX function does not allow 2 instances.

    Could someone please help?

    Regards,

    Steven.

  • September 13, 2010 at 4:10 am

    #1219930

    charindex returns the starting position of one item.

    to find two items, you need two searches;

    CHARINDEX ( ' emergency',CommentColumn) > 0 OR CHARINDEX( 'not secure',CommentColumn) > 0

    more likely, you want to grab comments and split them into multiple comments...

    so for example if you searched for the word "AND" in the first example you gave, you'll have two comments...the one before and the one after.

    for the second example you gave, you might split on the periods in the comment; in that case, you get three comments, two that ar erelevant, and one that is blank.

    are you familiar with the many SPLIT script contributions here on SSC?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 13, 2010 at 4:12 am

    #1219933

    Hi,

    No i do not know SPLIT scripts.

    Cheers,

    Steve.

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

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