Post reply

Need Charindex to Pick Up the Second Instance

  • October 28, 2011 at 3:53 pm

    #390715

    I have a sample of a table:

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..#JobName','U') IS NOT NULL DROP TABLE #DateTest

    GO

    --===== Create the test table

    CREATE TABLE #JobName(

    JobNameNVARCHAR(100)

    )

    --===== Insert the test data into the test table

    INSERT INTO #JobName (Item, OrderDate)

    SELECT 'InventoryCostReport_620013_SyteLine_HQ_App_Plt' UNION ALL

    SELECT 'Scheduling_3113457_SyteLine_HQ_App' UNION ALL

    SELECT 'ATP_CycleCountSummaryRpt_1792547_SyteLine_HQ_App' UNION ALL

    SELECT 'SCRM_EmployeeTardyReport_1301630_SyteLine_HQ_App' UNION ALL

    SELECT 'SPM_WarRoomReport_2695868_SyteLine_HQ_App' UNION ALL

    SELECT 'SP_WarRoomReportPastDue_3061579_SyteLine_HQ_App' UNION ALL

    SELECT 'TotalInventoryValuebyAcctReport_714769_SyteLine_HQ_App' UNION ALL

    SELECT 'VouchersPayableReport_3128272_SyteLine_HQ_App'

    --==== SELECT the records

    SELECT * FROM #JobName

    I want to pull all of the records everything from the number over truncated, like so:

    JobName

    -----------------------------------------------

    InventoryCostReport

    Scheduling

    ATP_CycleCountSummaryRpt

    SCRM_EmployeeTardyReport

    SPM_WarRoomReport

    SP_WarRoomReportPastDue

    TotalInventoryValuebyAcctReport

    VouchersPayableReport

    When I use this code:

    SELECT LEFT(JobName, charindex('_', JobName)-1) JobName FROM #JobName

    where substring(JobName, charindex('_', JobName)+1, 1) BETWEEN '1' AND '9'

    I get this result instead:

    JobName

    ----------------------------------

    InventoryCostReport

    Scheduling

    TotalInventoryValuebyAcctReport

    VouchersPayableReport

    How can I get the code to overlook the underscore when it is followed by a letter? It seems that it only looks at the first underscore, regardless.

    If you need any more information, please let me know.

    Thanks.

    Steve

  • October 29, 2011 at 1:29 am

    #1400812

    You need PATINDEX, try this:

    IF OBJECT_ID('TempDB..#JobName','U') IS NOT NULL

    DROP TABLE #JobName;

    GO

    --===== Create the test table

    CREATE TABLE #JobName(

    JobName NVARCHAR(100)

    );

    --===== Insert the test data into the test table

    INSERT INTO #JobName (JobName)

    SELECT 'InventoryCostReport_620013_SyteLine_HQ_App_Plt'

    UNION ALL

    SELECT 'Scheduling_3113457_SyteLine_HQ_App'

    UNION ALL

    SELECT 'ATP_CycleCountSummaryRpt_1792547_SyteLine_HQ_App'

    UNION ALL

    SELECT 'SCRM_EmployeeTardyReport_1301630_SyteLine_HQ_App'

    UNION ALL

    SELECT 'SPM_WarRoomReport_2695868_SyteLine_HQ_App'

    UNION ALL

    SELECT 'SP_WarRoomReportPastDue_3061579_SyteLine_HQ_App'

    UNION ALL

    SELECT 'TotalInventoryValuebyAcctReport_714769_SyteLine_HQ_App'

    UNION ALL

    SELECT 'VouchersPayableReport_3128272_SyteLine_HQ_App';

    --==== SELECT the records

    SELECT * FROM #JobName;

    select left(JobName, patindex('%_[0-9]%', JobName) - 1) from #JobName;

    drop table #JobName;

    By the way, the code you posted doesn't work, I needed to make changes to get it to work.

  • October 31, 2011 at 9:50 am

    #1401163

    Lynn,

    Thank you very much for the code, and also for correcting what I had sent. This works fine except for one thing; I get this message when I run it:

    Msg 536, Level 16, State 3, Line 1

    Invalid length parameter passed to the substring function.

    When I take out the '- 1' it works fine, but obviously gives me more everything.

  • October 31, 2011 at 10:58 am

    #1401221

    sdownen05 (10/31/2011)

    Lynn,

    Thank you very much for the code, and also for correcting what I had sent. This works fine except for one thing; I get this message when I run it:

    Msg 536, Level 16, State 3, Line 1

    Invalid length parameter passed to the substring function.

    When I take out the '- 1' it works fine, but obviously gives me more everything.

    Must be a data issue. I don't get that error with the data you provided. You should check the data in your table to see if there is something not accounted for in the sample data.

  • October 31, 2011 at 1:08 pm

    #1401263

    Add a NULLIF(patindex, 0).

    NULL -1 => NULL

    LEFT ('string', NULL) doesn't die on you.

  • October 31, 2011 at 3:42 pm

    #1401344

    You are right, Lynn. I didn't give you enough sample data. However, I have figured out a way around it. Thank you for your help.

    Steve

  • October 31, 2011 at 8:56 pm

    #1401401

    sdownen05 (10/31/2011)

    You are right, Lynn. I didn't give you enough sample data. However, I have figured out a way around it. Thank you for your help.

    Steve

    Two way street here, Steve. Please explain the work around you did. Thanks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 31, 2011 at 9:43 pm

    #1401405

    Not a problem, Jeff. Thanks for calling me on that.

    I only want records that end in '_Syteline_HQ_App', so I just did this:

    select left(JobName, patindex('%_[0-9]%', JobName) - 1)

    from #JobName where JobName like '%_Syteline_HQ_App'

    I know it doesn't solve everyone's problem, but it takes care of this one.

    However, I just noticed Ninja's_RGR'us post. That code is much better than mine.

    Thank you all for everything, and I will remember to contribute more. You all have taught me a lot in a short amount of time.

    Steve

  • November 1, 2011 at 4:27 am

    #1401487

    sdownen05 (10/31/2011)

    Not a problem, Jeff. Thanks for calling me on that.

    I only want records that end in '_Syteline_HQ_App', so I just did this:

    select left(JobName, patindex('%_[0-9]%', JobName) - 1)

    from #JobName where JobName like '%_Syteline_HQ_App'

    I know it doesn't solve everyone's problem, but it takes care of this one.

    However, I just noticed Ninja's_RGR'us post. That code is much better than mine.

    Thank you all for everything, and I will remember to contribute more. You all have taught me a lot in a short amount of time.

    Steve

    Glad it helped.

    In the best of both worlds you'd both filter the data down as much as possible and protect yourself from the left failing on you. Being able to do both without forcing a table scan is always a good option ;-).

    P.S. It's the same trick when doing avgs => SUM(a) / SUM(b) => Divide by 0 error. However / NULLIF(SUM(b), 0) won't fail on you either.

    Very useful to know :-).

  • November 1, 2011 at 7:30 am

    #1401600

    sdownen05 (10/31/2011)

    Not a problem, Jeff. Thanks for calling me on that.

    ....

    However, I just noticed Ninja's_RGR'us post. That code is much better than mine.

    Thanks, Steve. That's the spirit. Now you know the 2 reasons why I ask for such a thing...

    1. We all might learn something new from you especially since you're the closest to the problem.

    2. We do a double check on your code for you just to make sure. Think of it as a "peer review" to try to help keep you out of trouble. I've seen lots of folks coin their own solution from suggestions and have it turn out wrong.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 1, 2011 at 9:19 am

    #1401692

    I appreciate all of you looking out for me. I am realtively new at this, and I am trying to 1) establish best practices, and 2) get the data that is requested quickly. I am pretty much on my own, here, which I know all of you are all too familiar with.

    I don't mind at all that you ask me to share my code. I have been at this long enough that I need to start doing it anyway.

    Thank you for your support. 🙂

    Steve

  • November 1, 2011 at 10:03 am

    #1401722

    sdownen05 (11/1/2011)

    I appreciate all of you looking out for me. I am realtively new at this, and I am trying to 1) establish best practices, and 2) get the data that is requested quickly. I am pretty much on my own, here, which I know all of you are all too familiar with.

    I don't mind at all that you ask me to share my code. I have been at this long enough that I need to start doing it anyway.

    Thank you for your support. 🙂

    Steve

    That's how the community grows and ultimately, doesn't need itself for support at some point. 😉

Viewing 12 posts - 1 through 11 (of 11 total)

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