using CHARINDEX in SUBSTRING, need start and end
-
This works, but I'm wondering if there is a better way to do it. The number is never more than 99.
I was thinking just do:
SUBSTRING(ActionId, CHARINDEX('pg',ActionId) + 2, 3)
then do something that only recognizes the numeric value. That way if they ever go above 99, I dont need to change the code.
Any ideas would be appreciated. Thanks!
DECLARE @FileName as varchar(100)
SELECT @FileName = 'Watch_pg4of4_812006.csv'
SELECT @FileName = 'Watch_pg24of4_812006.csv'
SELECT
--CHARINDEX('pg',ActionId),
--CHARINDEX('of',ActionId),
SUBSTRING(ActionId, CHARINDEX('pg',ActionId) + 2, CASE
WHEN CHARINDEX('of',ActionId) - CHARINDEX('pg',ActionId) = 3 THEN 1
ELSE 2 END)
FROM LogActivity
--Watch_pg4of4_812006.csv
--Watch_pg24of4_812006.csv
-
I'm thinking strpping anything other than numerics is the way to go.
That way if someone puts an alpha in the wrong spot, I'm going to always end up with only numerics (I cast the result into a int and use the value, so it has to be a number).
The thread shoud be named, numerics only.
-
Your first one should work, but I'd be wary of stripping the alphas. They are your markers. Otherwise how do you know if "244" is 2 of 44 or 24 of 4
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
24 of 4????
Good point however
. -
SUBSTRING(ActionId, CHARINDEX('pg',ActionId) + 2, 3)
If I grab just 3 characters after 'pg' it should always be:
one numeric and 'of'
or
two numerics and 'o'
So stripping alphas at that point is just looking at a three character string.
-
This should do the trick... the SELECT is where the rubber meets the road... the rest of the stuff is just setting up for the demo of the SELECT...
--===== If the test table exists, drop it
IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL
DROP TABLE #MyHead
--===== Create the test table
CREATE TABLE #MyHead (FileName VARCHAR(100) PRIMARY KEY)
--===== Populate the test table
INSERT INTO #MyHead (FileName)
SELECT 'Watch_pg1of2_812006.csv' UNION ALL
SELECT 'Watch_pg1of20_812006.csv' UNION ALL
SELECT 'Watch_pg10of20_812006.csv' UNION ALL
SELECT 'Watch_pg1of999_812006.csv' UNION ALL
SELECT 'Watch_pg20of999_812006.csv' UNION ALL
SELECT 'Watch_pg900of999_812006.csv'
--===== Demo the solution to the problem
SELECT FileName,
SUBSTRING(FileName,
CHARINDEX('pg',FileName)+2,
CHARINDEX('of',FileName)-CHARINDEX('pg',FileName)-2
) AS X,
SUBSTRING(FileName,
CHARINDEX('of',FileName)+2,
CHARINDEX('_',REPLACE(FileName,'_pg','-pg'))-CHARINDEX('of',FileName)-2
) AS Y
FROM #MyHead
--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)
Intro to Tally Tables and Functions -
In reply to Jeff Moden's post:
I was wondering why you're using a replace in the SUBSTRING command to select the value for Y? Wouldn't it be easier to supply the second CHARINDEX command with a starting position?
I suppose it does depend on the expected filenames. In your example they all start with 'Watch_pg' so there shouldn't be a problem to tell the second CHARINDEX command to search starting from the ninth position in the string. I don't think it creates more of a dependency on the filename convention used as you do now with the replace function. I do think however that your query will run faster as I've always been told that string manipulation is expensive.
The SELECT statement would become:
SELECT FileName,
SUBSTRING(FileName,
CHARINDEX('pg',FileName)+2,
CHARINDEX('of',FileName)-CHARINDEX('pg',FileName)-2
) AS X,
SUBSTRING(FileName,
CHARINDEX('of',FileName)+2,
CHARINDEX('_',FileName,9)-CHARINDEX('of',FileName)-2
) AS Y
FROM #MyHead
-
Nope... couldn't do that because the first underscore will interfere with finding the second underscore.
--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)
Intro to Tally Tables and Functions -
No it wouldn't. Not if you tell the CHARINDEX to start looking for an underscore after the position of the first underscore. Since the prefix of the file names seems to be fixed this is not a problem.
Try the SELECT statement I provided combined with your statements to setup the temporary table. You'll see it works just fine. I'll grant you that it doesn't show a huge improvement in speed but I feel confident that the difference will become clearer as the number of rows grows.
-
Sorry... missed the fact that you used "9" as the starting point in the CHARINDEX...
Works great if the prefix is always "Watch" or some other combo of 5 letters... if it changes, you will need to change your code and I won't. But, I think your's would win the speed match because it has not only one less function, it also ignores the first 9 characters... nice job.
--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)
Intro to Tally Tables and Functions
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply