June 11, 2012 at 3:46 pm
thank you Lynn, that works great.
--Quote me
June 11, 2012 at 3:51 pm
polkadot (6/11/2012)
thank you Lynn, that works great.
Which works?
June 12, 2012 at 6:26 am
try this
select SUBSTRING(
substring('\Beverages\Soda Pop\Mountain Dew',2, len('\Beverages\Soda Pop\Mountain Dew')),
(
CHARINDEX('\',
substring('\Beverages\Soda Pop\Mountain Dew',2, len('\Beverages\Soda Pop\Mountain Dew') ))
)
,len('\Beverages\Soda Pop\Mountain Dew') )
June 12, 2012 at 11:36 am
The problem is complicated by this: the values I need to retain (before the first backslash) also have a prefix that needs to be removed.
So two things need to happen:
1)remove everything after the backslash (backslash included)
2)and remove Big_Fam_, Lcal_, or Lcost_
But how to combine tasks so both are accomplished?
---Modified DDL 'before'
SELECT d.ProdPath
INTO #TestTable
FROM (
SELECT 'Big_Fam_Beverages\Soda Pop\Mountain Dew' UNION ALL
SELECT 'Lcal_Beverages\Soda Pop' UNION ALL
SELECT 'lcost_Beverages' UNION ALL
SELECT 'Big_Fam_SomethingDifferent\' UNION ALL
SELECT 'lcost_Partial\Listing'
) d (ProdPath);
Select * from #testtable;
---cleansing tasks. I can run them seperately...but don't know how to combine.
SELECT
CASE
when CHARINDEX('\', ProdPath) > 0
then LEFT(ProdPath, CHARINDEX('\', ProdPath) - 1)
ELSE ProdPath
end as Application
--case
-- when ProdPath like '%big_fam_%' then SUBSTRING(ProdPath, 9, 100)
-- when ProdPath like '%Lcost_%' then SUBSTRING(ProdPath, 7, 100)
-- when ProdPath like '%Lcal_%' then SUBSTRING(ProdPath, 6, 100)
--end
From
#TestTable
GO
How it should look when cleansed "after":
SELECT d.ProdPath
INTO #TestTable1
FROM (
SELECT 'Beverages' UNION ALL
SELECT 'Beverages' UNION ALL
SELECT 'Beverages' UNION ALL
SELECT 'SomethingDifferent' UNION ALL
SELECT 'Partial'
) d (ProdPath);
select * from #testtable1
--Quote me
June 12, 2012 at 12:49 pm
polkadot (6/12/2012)
The problem is complicated by this: the values I need to retain (before the first backslash) also have a prefix that needs to be removed.So two things need to happen:
1)remove everything after the backslash (backslash included)
2)and remove Big_Fam_, Lcal_, or Lcost_
But how to combine tasks so both are accomplished?
---Modified DDL 'before'
SELECT d.ProdPathINTO #TestTable
FROM (
SELECT 'Big_Fam_Beverages\Soda Pop\Mountain Dew' UNION ALL
SELECT 'Lcal_Beverages\Soda Pop' UNION ALL
SELECT 'lcost_Beverages' UNION ALL
SELECT 'Big_Fam_SomethingDifferent\' UNION ALL
SELECT 'lcost_Partial\Listing'
) d (ProdPath);
Select * from #testtable;
---cleansing tasks. I can run them seperately...but don't know how to combine.
SELECTCASE
when CHARINDEX('\', ProdPath) > 0
then LEFT(ProdPath, CHARINDEX('\', ProdPath) - 1)
ELSE ProdPath
end as Application
--case
-- when ProdPath like '%big_fam_%' then SUBSTRING(ProdPath, 9, 100)
-- when ProdPath like '%Lcost_%' then SUBSTRING(ProdPath, 7, 100)
-- when ProdPath like '%Lcal_%' then SUBSTRING(ProdPath, 6, 100)
--end
From
#TestTable
GO
How it should look when cleansed "after":
SELECT d.ProdPathINTO #TestTable1
FROM (
SELECT 'Beverages' UNION ALL
SELECT 'Beverages' UNION ALL
SELECT 'Beverages' UNION ALL
SELECT 'SomethingDifferent' UNION ALL
SELECT 'Partial'
) d (ProdPath);
select * from #testtable1
First, I would like to ask you to give us the big picture, not little snippets here and there of what you are trying to accomplish. Everytime we think we have answered your question we get another "but there's more..." response.
Here is some updated code:
SELECT d.ProdPath
INTO #TestTable
FROM
(
SELECT 'Big_Fam_Beverages\Soda Pop\Mountain Dew' UNION ALL
SELECT 'Lcal_Beverages\Soda Pop' UNION ALL
SELECT 'lcost_Beverages' UNION ALL
SELECT 'Big_Fam_SomethingDifferent\' UNION ALL
SELECT 'lcost_Partial\Listing') d (ProdPath);
Select * from #testtable;
SELECT
REVERSE(LEFT(REVERSE(LEFT(ProdPath, CHARINDEX('\', ProdPath + '\') - 1)), CHARINDEX('_', REVERSE(LEFT(ProdPath, CHARINDEX('\', ProdPath + '\') - 1))) - 1)) as Application
From
#TestTable
GO
DROP TABLE #TestTable;
GO
June 12, 2012 at 1:23 pm
Lynn, But, you helped me. You might admit, that one is logistically quite complicated ...and utilizing a function I have not run into yet. This worked. This was the answer.
SELECT REVERSE(LEFT(REVERSE(LEFT(ProdPath, CHARINDEX('\', ProdPath + '\') - 1)), CHARINDEX('_', REVERSE(LEFT(ProdPath, CHARINDEX('\', ProdPath + '\') - 1))) - 1)) as Application From #TestTable
:hehe:
Thank you!
--Quote me
June 12, 2012 at 1:37 pm
Here's something a bit flexible for this kind of thing:
DECLARE @String VARCHAR(1000) = 'Bev\Soda\The Dew',
@Delim CHAR(1) = '\',
@Elements INT = 0,
@Start INT = 2 ;
--DECLARE @String VARCHAR(1000) = 'Bev', @Delim CHAR(1) = '\', @Elements INT = 0, @Start INT = 2;
SELECT @String ;
IF @Elements = 0
SET @Elements = LEN(@String) ;
SELECT STUFF((SELECT @Delim + Sub
FROM (SELECT TOP (@Elements)
Sub
FROM (SELECT Number,
ROW_NUMBER() OVER (ORDER BY Number) AS Position,
SUBSTRING(@String, Number + 1,
ISNULL(NULLIF(CHARINDEX(@Delim, @String, Number + 1), 0) - Number - 1,
LEN(@String))) AS Sub
FROM dbo.Numbers
WHERE Number <= LEN(@String)
AND (SUBSTRING(@String, Number, 1) = @Delim
OR Number = 0)) AS Splitter
WHERE Position >= @Start
ORDER BY Number) AS Aggregator
FOR XML PATH(''),
TYPE).value('.[1]', 'VARCHAR(1000)'), 1, 1, '') ;
It requires having a Numbers table that goes from 0 to whatever length you plan to handle. I keep one with 0 to 10-thousand. Just sequential integer numbers.
You can create that on-the-fly by using a very efficient CTE, if you want a small performance improvement. Up to you.
What this will do is take an input string, a delimiter, a number of elements deep you want to go (0 = "all the way"), and what element you want to start at.
So, if you want just the 2nd and on, put a 2 in the @Start variable and 0 in the Elements table. I have a sample for that, commented out, in the above. Uncomment that, and comment out the first test, and you'll see how it works.
If you want just the 1st, put 1 in both @Start and @Elements.
If you want 2nd and 3rd, put 2 in @Start and 2 in @Elements. (@Elements is the quantity of elements, not the position of the last element.)
If you want something that takes the position of the last desired element, add @End to the variables, and add "Position <= @End" after "Position >= @Start", and maybe wrap that in a NullIf() so that you can make 0 = infinite. "and Position <= IsNull(NullIf(@End, 0), Len(@String))" right after "Position >= @Start".
Gives you one function that will do most/all of what you might want with this kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 12, 2012 at 1:52 pm
Had another thought on this. Modified version:
DECLARE @String VARCHAR(1000) = 'Bev\Soda\The Dew',
@Delim CHAR(1) = '\',
@Elements INT = -1, -- 0 = infinite, negative = last X elements instead of first X
@Start INT = 1,
@End INT = 0 ; -- 0 = infinite
--DECLARE @String VARCHAR(1000) = 'Bev', @Delim CHAR(1) = '\', @Elements INT = 0, @Start INT = 2;
SELECT @String ;
IF @Elements = 0
SET @Elements = LEN(@String) ;
SELECT STUFF((SELECT @Delim + Sub
FROM (SELECT TOP (ABS(@Elements))
Sub
FROM (SELECT Number,
ROW_NUMBER() OVER (ORDER BY Number) AS Position,
SUBSTRING(@String, Number + 1,
ISNULL(NULLIF(CHARINDEX(@Delim, @String, Number + 1), 0) - Number - 1,
LEN(@String))) AS Sub
FROM dbo.Numbers
WHERE Number <= LEN(@String)
AND (SUBSTRING(@String, Number, 1) = @Delim
OR Number = 0)) AS Splitter
WHERE Position >= @Start
AND Position <= ISNULL(NULLIF(@End, 0), LEN(@String))
ORDER BY @Elements * Number) AS Aggregator
FOR XML PATH(''),
TYPE).value('.[1]', 'VARCHAR(1000)'), 1, 1, '') ;
Add in the @Last parameter, as discussed in the text of my prior post.
Also made it so you can enter a negative value for @Elements, and get the end of the string instead of the beginning. If, for example, you just want the very last element, put -1 in there, and it'll get it for you. If you want the last 2, put -2.
Could also modify it to get the Xth-to-last, but I think I'm probably going overboard already, so I'll leave that up to someone who needs it to work that piece out. Just takes playing with the Position value from the Splitter sub-query. (Add in a "ReversePosition" column for something like that?)
EDIT: Cleaned up Order By clause.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 27, 2012 at 9:54 pm
Gsquared, will you please show me how to use your "Here's something a bit flexible for this kind of thing:"?
I have another example.
Two things need to happen:
1)remove everything after the second backslash (backslash included)
2)and remove \Big_Fam_, \Lcal_, or \Lcost_
--Before
SELECT d.ProdPath
INTO #TestTable
FROM (
SELECT '\Big_Fam_Beverages\Soda Pop\Mountain Dew' UNION ALL
SELECT '\Lcal_Beverages\Soda Pop' UNION ALL
SELECT '\lcost_Beverages' UNION ALL
SELECT '\Big_Fam_SomethingDifferent\' UNION ALL
SELECT '\lcost_Partial\Listing'
) d (ProdPath);
Select * from #testtable;
--After
SELECT d.ProdPath
INTO #TestTable1
FROM (
SELECT 'Beverages' UNION ALL
SELECT 'Beverages' UNION ALL
SELECT 'Beverages' UNION ALL
SELECT 'SomethingDifferent' UNION ALL
SELECT 'Partial'
) d (ProdPath);
Lynn helped me with very similar situation, but there wasn't a backslash at the beginning of each string.
--Quote me
June 27, 2012 at 10:16 pm
I'm not GSquared (nor do I pretend to be), but this may work for you:
;WITH TestData (MyStr) AS (
SELECT '\Big_Fam_Beverages\Soda Pop\Mountain Dew'
UNION ALL SELECT '\Lcal_Beverages\Soda Pop'
UNION ALL SELECT '\lcost_Beverages'
UNION ALL SELECT '\Big_Fam_SomethingDifferent\'
UNION ALL SELECT '\lcost_Partial\Listing')
SELECT MyStr=REVERSE(SUBSTRING(REVERSE(MyStr3), 1, CHARINDEX('_', REVERSE(MyStr3))-1))
FROM TestData
CROSS APPLY (SELECT LTRIM(REPLACE(MyStr + '\', '\', ' '))) a(MyStr2)
CROSS APPLY (SELECT SUBSTRING(MyStr2, 1, CHARINDEX(' ', MyStr2)-1)) b(MyStr3)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 27, 2012 at 10:40 pm
Or this?
SELECT REVERSE( LEFT (crsapp.r , CHARINDEX('_', crsapp.r)-1))
from #testtable t
CROSS APPLY ( SELECT t.ProdPath + '\' ) P(ProdPath)
CROSS APPLY ( SELECT REVERSE ( SUBSTRING(P.ProdPath, 2, CHARINDEX('\',P.ProdPath,2)-2 ) ) ) crsapp (r)
;
June 27, 2012 at 10:58 pm
I don't see how the substitution of my actual column name would work in either dwain or ColdCoffee's case. 🙁
SELECT MyStr=REVERSE(SUBSTRING(REVERSE(MyStr3), 1, CHARINDEX('_', REVERSE(MyStr3))-1))
FROM TestData
CROSS APPLY (SELECT LTRIM(REPLACE(MyStr + '\', '\', ' '))) a(MyStr2)
CROSS APPLY (SELECT SUBSTRING(MyStr2, 1, CHARINDEX(' ', MyStr2)-1)) b(MyStr3)
what is MyStr, MyStr1, MyStr2?:w00t:
SELECT REVERSE( LEFT (crsapp.r , CHARINDEX('_', crsapp.r)-1))
from #testtable t
CROSS APPLY ( SELECT t.ProdPath + '\' ) P(ProdPath)
CROSS APPLY ( SELECT REVERSE ( SUBSTRING(P.ProdPath, 2, CHARINDEX('\',P.ProdPath,2)-2 ) ) ) crsapp (r)
;
what goes in t.ProdPath, P(ProdPath) crsapp(r)?:w00t:
--Quote me
June 27, 2012 at 11:04 pm
MyStr, MyStr1 and MyStr2 in my version are just the names of the computed fields which you can substitute for your actual column as needed.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 28, 2012 at 3:18 am
hi,
try this, may its works for u.
declare @string varchar(200)
set @string = '\Beverages\Soda Pop\Mountain Dew'
if CHARINDEX('\',@string,CHARINDEX('\',@string)+1)>0
set @string= STUFF( @string , 1 , CHARINDEX('\',@string,CHARINDEX('\',@string)+1),'')
else
set @string = ' '
print @string
June 28, 2012 at 3:25 am
Hi,
try this, may it works for u
declare @string varchar(200)
set @string = '\Beverages\Soda Pop\Mountain Dew'
if CHARINDEX('\',@string,CHARINDEX('\',@string)+1)>0
set @string= STUFF( @string , 1 , CHARINDEX('\',@string,CHARINDEX('\',@string)+1),'')
else
set @string= ' '
print @string
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply