Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2011
    Posts
    30

    Unanswered: substring ands charindex

    hi all..

    Thanks for the help in my previous post..

    my "SELECT Itemcode, MAX( ItemDesc )"

    give me this
    "
    0057132;DOCUMENT TITLE: abc12345535256, Sept to Nov 2004, PROJECT: N/A, EQUIPMENT NUMBER: N/A, DEPARTMENT: DESCRIPTION/REMARKS: Binder, RETENTION:
    "

    Now i want it to only generate
    "
    DOCUMENT TITLE: abc12345535256, Sept to Nov 2004,
    "
    I have tried :

    SELECT Itemcode, SUBSTRING(MAX( ItemDesc ), CHARINDEX('PROJECT',MAX( ItemDesc )), CHARINDEX(':',MAX( ItemDesc )))

    which gives me "
    PROJECT: N/A, E
    "

    So i am going to try this "
    SELECT Itemcode, SUBSTRING(MAX( ItemDesc ), CHARINDEX('DOCUMENT',MAX( ItemDesc COLLATE Latin1_General_CS_AS)), CHARINDEX('PROJECT',MAX( ItemDesc COLLATE Latin1_General_CS_AS)))
    "
    Since i want it to only capture the out from "Document" to/before "PROJECT" ..

    Any ideas on if i am heading down the right path ?
    Last edited by drdre; 02-28-11 at 20:11.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by drdre View Post
    Any ideas on if i am heading down the right path ?
    yes, you are
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2011
    Posts
    30

    inaripan samkhya

    Thanks r937 it works..

    but it displays the "P" from Projects.

    Is there anyway around this ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by drdre View Post
    Is there anyway around this ?
    more tweakage

    syntax: SUBSTRING(expression,start,length)

    for length, you have CHARINDEX('PROJECT'...) -- this is wrong

    i think the length you want is CHARINDEX('PROJECT'...) - CHARINDEX('DOCUMENT'...)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2011
    Posts
    30
    r937

    if i have it as
    SELECT Itemcode, SUBSTRING(MAX( ItemDesc ), CHARINDEX('Project',MAX( ItemDesc COLLATE Latin1_General_CS_AS)), CHARINDEX('Document',MAX( ItemDesc COLLATE Latin1_General_CS_AS)))

    It generates from the word Project onwards..

    when i have it as

    SELECT Itemcode, SUBSTRING(MAX( ItemDesc ), CHARINDEX('DOCUMENT',MAX( ItemDesc COLLATE Latin1_General_CS_AS)), CHARINDEX('PROJECT',MAX( ItemDesc COLLATE Latin1_General_CS_AS)))

    It starts with "Document" which is right.. and ends at "P" at the start of "Project".. I just want it to stop and not show the "P" so i am wondering... if i should put a space before the word project..as in

    "SELECT Itemcode, SUBSTRING(MAX( ItemDesc ), CHARINDEX('DOCUMENT',MAX( ItemDesc COLLATE Latin1_General_CS_AS)), CHARINDEX(' PROJECT',MAX( ItemDesc COLLATE Latin1_General_CS_AS)))"

    Going to log into the server and give that a test...

    thanks for all the help.

  6. #6
    Join Date
    Jun 2005
    Posts
    319
    Code:
    DECLARE @parse varchar(max)
    
    SET @parse = '0057132;DOCUMENT TITLE: abc12345535256, Sept to Nov 2004, PROJECT: N/A,'
    
    SELECT SUBSTRING(@parse, CHARINDEX(';', @PARSE)+1, CHARINDEX('PROJECT', @PARSE)-CHARINDEX(';', @PARSE)-1)
    Code:
    DOCUMENT TITLE: abc12345535256, Sept to Nov 2004,

  7. #7
    Join Date
    Feb 2011
    Posts
    30
    Guys its runs as is with the " PROJECT" the space before works

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •