October 28, 2011 at 3:53 pm
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
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
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
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
Add a NULLIF(patindex, 0).
NULL -1 => NULL
LEFT ('string', NULL) doesn't die on you.
October 31, 2011 at 3:42 pm
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
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
Change is inevitable... Change for the better is not.
October 31, 2011 at 9:43 pm
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
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
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
Change is inevitable... Change for the better is not.
November 1, 2011 at 9:19 am
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
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