Post reply

char index in substring

  • December 16, 2011 at 6:54 am

    #146270

    Hi

    while I am running this query

    select substring(cnsmr_accnt.cnsmr_accnt_crdtr_rfrnc_id_txt,1,charindex('-',cnsmr_accnt.cnsmr_accnt_crdtr_rfrnc_id_txt)-1)

    from cnsmr_accnt

    I am getting an error like this ,

    Msg 537, Level 16, State 2, Line 2

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    please can any one help me?????

  • December 16, 2011 at 7:10 am

    #1422489

    I'm guessing that [font="Courier New"]charindex('-',cnsmr_accnt.cnsmr_accnt_crdtr_rfrnc_id_txt)-1[/font] evaluates to 0 or less.

    John

  • December 16, 2011 at 7:37 am

    #1422503

    Thank you for your reply John.

    then can you tell me how can I avoid that?

  • December 16, 2011 at 7:43 am

    #1422509

    Yes. Make sure that "-" appears in cnsmr_accnt.cnsmr_accnt_crdtr_rfrnc_id_txt at position 2 or later. Read the Books Online pages for CHARINDEX and SUBSTRING so that you understand how they work.

    John

  • December 16, 2011 at 9:18 am

    #1422593

    Just for fun, or debugging purposes, i tend to run queries like this in the following way until I can see that i've ironed out the issues -->

    select

    cnsmr_accnt.cnsmr_accnt_crdtr_rfrnc_id_txt AS Field_Im_Parsing,

    charindex('-',cnsmr_accnt.cnsmr_accnt_crdtr_rfrnc_id_txt)-1 AS [What_I_Think_should_be_greaterthan_zero]

    -- substring(cnsmr_accnt.cnsmr_accnt_crdtr_rfrnc_id_txt,1,charindex('-',cnsmr_accnt.cnsmr_accnt_crdtr_rfrnc_id_txt)-1)

    FROM

    Wherever

    and usually with a where clause like

    WHERE

    charindex('-',cnsmr_accnt.cnsmr_accnt_crdtr_rfrnc_id_txt)-1 <= 0

    THis will show you the components of the formula you're trying to evaluate.

    HTH,

    Steve.

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

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