Results 1 to 3 of 3

Thread: Charindex

  1. #1
    Join Date
    Jun 2011
    Posts
    6

    Unanswered: Charindex

    Dear all,

    Declare @key varchar(100)
    Set @key = 'Total Price including Commission at This Level: '

    Select
    SUBSTRING(calculation,start,finish-start)
    From
    (
    Select
    calculation,
    start = charindex(@key,calculation,1)+LEN(@key)+1,
    finish = charindex(' ',calculation,charindex(@key,calculation,1)+LEN(@k ey)+1)
    From pr_cal
    ) A

    Result:
    It returns few rows for eg:
    -3.6847058823529411764705882353<br
    -7.4565766786786564342346795345<br
    -3.6842684690456865422468005352<br

    and then i got "Invalid length parameter passed to the LEFT or SUBSTRING function" error

    Please any one can let me know, How to amend this Query to avoid the above error and i need only value for ex -3.684.Thanks.

    Regards,
    SG

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Most of the times I get this error, it is due to rows that does not contain the search pattern. Keep in mind that whenever search pattern is not found, it returns 0. For your code to work as intended, finish have to be greater than start.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    DROP TABLE #MyTable
    CREATE TABLE #MyTable(
    	calculation	VARCHAR(500)	NOT NULL
    )
    
    INSERT INTO #MyTable VALUES
    ('This Level: 12345 hjhj hjh hj hj This Level: '),
    (' gh ghj ghf gdhfj This Level: '),
    ('hj hjh jhjh j')
    
    
    Declare @key varchar(100)
    Set @key = 'Level: '
    
    Select	SUBSTRING(
    	calculation, 
    	charindex(@key, calculation, 1) + LEN(@key) + 1,
    	charindex(' ', calculation, charindex(@key, calculation, 1) + LEN(@key) + 1) - 
    		(charindex(@key, calculation, 1) + LEN(@key) + 1)
    	)
    From #MyTable
    WHERE charindex(@key, calculation, 1) > 0 AND
    	charindex(' ', calculation, charindex(@key, calculation, 1) + LEN(@key) + 1) > 0
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

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