September 26, 2016 at 10:57 am
I need a SQL statement that will out put the following values:
( The objective is to grab any values between the first and third "\" character
Sample Output
"/MidwestHealthPlan/Claims/"
"/MidwestHealthPlan/Authorization/"
CREATE TABLE #t( pathx VARCHAR(400) ) ;
INSERT INTO #t ( pathx ) VALUES ( '/CaseRateAnalysis/');
INSERT INTO #t ( pathx ) VALUES ( '/MedicaidBV250/');
INSERT INTO #t ( pathx ) VALUES ( '/Midwest Advantage/');
INSERT INTO #t ( pathx ) VALUES ( '/MidwestHealthPlan/Authorization/');
INSERT INTO #t ( pathx ) VALUES ( '/MidwestHealthPlan/Authorization/IT/');
INSERT INTO #t ( pathx ) VALUES ( '/MidwestHealthPlan/Claims/Archive Report/');
INSERT INTO #t ( pathx ) VALUES ( '/MidwestHealthPlan/Claims/Archive Report/ClaimsAfterTerm/');
September 26, 2016 at 11:12 am
mw112009 (9/26/2016)
I need a SQL statement that will out put the following values:( The objective is to grab any values between the first and third "\" character
Sample Output
"/MidwestHealthPlan/Claims/"
"/MidwestHealthPlan/Authorization/"
CREATE TABLE #t( pathx VARCHAR(400) ) ;
INSERT INTO #t ( pathx ) VALUES ( '/CaseRateAnalysis/');
INSERT INTO #t ( pathx ) VALUES ( '/MedicaidBV250/');
INSERT INTO #t ( pathx ) VALUES ( '/Midwest Advantage/');
INSERT INTO #t ( pathx ) VALUES ( '/MidwestHealthPlan/Authorization/');
INSERT INTO #t ( pathx ) VALUES ( '/MidwestHealthPlan/Authorization/IT/');
INSERT INTO #t ( pathx ) VALUES ( '/MidwestHealthPlan/Claims/Archive Report/');
INSERT INTO #t ( pathx ) VALUES ( '/MidwestHealthPlan/Claims/Archive Report/ClaimsAfterTerm/');
Probably something like this can work.
SELECT DISTINCT
SUBSTRING( pathx, first.location, CHARINDEX( '/', pathx, CHARINDEX('/', pathx, first.location + 1) - first.location + 2))
FROM #t
CROSS APPLY( SELECT CHARINDEX('/', pathx) AS location) first
WHERE pathx LIKE '%/%/%/%';
September 26, 2016 at 11:18 am
Quick alternative to Luis's fine solution
😎
IF OBJECT_ID(N'tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t( pathx VARCHAR(400) ) ;
INSERT INTO #t ( pathx ) VALUES ( '/CaseRateAnalysis/');
INSERT INTO #t ( pathx ) VALUES ( '/MedicaidBV250/');
INSERT INTO #t ( pathx ) VALUES ( '/Midwest Advantage/');
INSERT INTO #t ( pathx ) VALUES ( '/MidwestHealthPlan/Authorization/');
INSERT INTO #t ( pathx ) VALUES ( '/MidwestHealthPlan/Authorization/IT/');
INSERT INTO #t ( pathx ) VALUES ( '/MidwestHealthPlan/Claims/Archive Report/');
INSERT INTO #t ( pathx ) VALUES ( '/MidwestHealthPlan/Claims/Archive Report/ClaimsAfterTerm/');
SELECT
TM.pathx
,SUBSTRING(TM.pathx,1,ISNULL(NULLIF(XXP.POS,0),XP.POS)) AS STRING_PART
FROM #t TM
OUTER APPLY
(
SELECT CHARINDEX(N'/',TM.pathx,2)
) AS XP(POS)
OUTER APPLY
(
SELECT CHARINDEX(N'/',TM.pathx,XP.POS + 1)
) AS XXP(POS);
Output from the sample data
pathx STRING_PART
------------------------------------------------------------ -----------------------------------
/CaseRateAnalysis/ /CaseRateAnalysis/
/MedicaidBV250/ /MedicaidBV250/
/Midwest Advantage/ /Midwest Advantage/
/MidwestHealthPlan/Authorization/ /MidwestHealthPlan/Authorization/
/MidwestHealthPlan/Authorization/IT/ /MidwestHealthPlan/Authorization/
/MidwestHealthPlan/Claims/Archive Report/ /MidwestHealthPlan/Claims/
/MidwestHealthPlan/Claims/Archive Report/ClaimsAfterTerm/ /MidwestHealthPlan/Claims/
September 26, 2016 at 11:24 am
Eirikur Eiriksson:
Sorry, but the first 2 entries in your solution should not be there. The output that I wanted will have exactly 3 "\" characters included.
I will go with the LUIS solution.
Thx
September 26, 2016 at 11:29 am
mw112009 (9/26/2016)
Eirikur Eiriksson:Sorry, but the first 2 entries in your solution should not be there. The output that I wanted will have exactly 3 "\" characters included.
I will go with the LUIS solution.
Thx
Do you understand that it's only a matter of adding the WHERE clause?
September 26, 2016 at 11:33 am
Different option:
SELECT '/' + LEFT( newpathx, CHARINDEX( '/', newpathx, CHARINDEX('/', newpathx) + 1))
FROM #t
CROSS APPLY( SELECT STUFF( pathx, 1, CHARINDEX('/', pathx), '') newpathx) x
WHERE pathx LIKE '%/%/%/%';
September 26, 2016 at 11:41 am
Luis Cazares (9/26/2016)
mw112009 (9/26/2016)
Eirikur Eiriksson:Sorry, but the first 2 entries in your solution should not be there. The output that I wanted will have exactly 3 "\" characters included.
I will go with the LUIS solution.
Thx
Do you understand that it's only a matter of adding the WHERE clause?
As Luis rightly pointed out it only needs the WHERE clause;-)
😎
SELECT DISTINCT
SUBSTRING(TM.pathx,1,ISNULL(NULLIF(XXP.POS,0),XP.POS)) AS STRING_PART
FROM #t TM
OUTER APPLY
(
SELECT CHARINDEX(N'/',TM.pathx,2)
) AS XP(POS)
OUTER APPLY
(
SELECT CHARINDEX(N'/',TM.pathx,XP.POS + 1)
) AS XXP(POS)
WHERE XXP.POS > 0;
September 26, 2016 at 12:08 pm
For fun, here's another option. This is a function I created a couple months ago which uses NGrams8K[/url]. The function allows you to grab everything between the mth and nth delimiter. Here's the function:
CREATE FUNCTION [dbo].[substringBetween8K]
(
@string varchar(8000),
@start tinyint,
@stop tinyint,
@delimiter char(1)
)
/*****************************************************************************************
Purpose:
Takes in input string (@string) and returns the text between two instances of a delimiter
(@delimiter); the location of the delimiters is defined by @start and @stop.
For example: if @string = 'xx.yy.zz.abc', @start=1, @stop=3, and @delimiter = '.' the
function will return the text: yy.zz; this is the text between the first and third
instance of "." in the string "xx.yy.zz.abc".
Compatibility:
SQL Server 2008+
Syntax:
--===== Autonomous use
SELECT sb.token, sb.position, sb.tokenLength
FROM dbo.substringBetween8K(@string, @start, @stop, @delimiter); sb;
--===== Use against a table
SELECT sb.token, sb.position, sb.tokenLength
FROM SomeTable st
CROSS APPLY dbo.substringBetween8K(st.SomeColumn1, 1, 2, '.') sb;
Parameters:
@string = varchar(8000); Input string to parse
@delimiter = char(1); this is the delimiter use to determine where the output starts/ends
@start = tinyint; the first instance of @delimiter to search for; this is where the
output should start. When @start is 0 then the function will return
everything from the beginning of @string until @end.
@stop = tinyint; the last instance of @delimiter to search for; this is where the
output should end. When @end is 0 then the function will return everything
from @start until the end of the string.
Return Types:
Inline Table Valued Function returns:
token = varchar(8000); the substring between the two instances of @delimiter defined by
@start and @stop
position = smallint; the location of where the substring begins
tokenlength = length of the return token
---------------------------------------------------------------------------------------
Developer Notes:
1. Requires NGrams8K. The code for NGrams8K can be found here:
http://www.sqlservercentral.com/articles/Tally+Table/142316/
2. This function is what is referred to as an "inline" scalar UDF." Technically it's an
inline table valued function (iTVF) but performs the same task as a scalar valued user
defined function (UDF); the difference is that it requires the APPLY table operator
to accept column values as a parameter. For more about "inline" scalar UDFs see this
article by SQL MVP Jeff Moden: http://www.sqlservercentral.com/articles/T-SQL/91724/
and for more about how to use APPLY see the this article by SQL MVP Paul White:
http://www.sqlservercentral.com/articles/APPLY/69953/.
Note the above syntax example and usage examples below to better understand how to
use the function. Although the function is slightly more complicated to use than a
scalar UDF it will yield notably better performance for many reasons. For example,
unlike a scalar UDFs or multi-line table valued functions, the inline scalar UDF does
not restrict the query optimizer's ability generate a parallel query execution plan.
3. dbo.substringBetween8K is deterministic; for more about deterministic and
nondeterministic functions see https://msdn.microsoft.com/en-us/library/ms178091.aspx
Examples:
DECLARE @string varchar(8000) = '123.ABC456.333.222.3333XXX.$$$'
-- beginning of string to 2nd delimiter, 2nd delimiter to end of the string
SELECT '0, 2', * FROM dbo.substringBetween8K(@string,0,2, '.') UNION ALL
SELECT '2, 0', * FROM dbo.substringBetween8K(@string,2,0, '.') UNION ALL
-- Between the 1st & 2nd, then 2nd & 5th delimiters
SELECT '1, 2', * FROM dbo.substringBetween8K(@string,1,2, '.') UNION ALL
SELECT '2, 5', * FROM dbo.substringBetween8K(@string,2,5, '.') UNION ALL
-- dealing with NULLS, delimiters that don't exist and when @first = @Last
SELECT '2, 10', * FROM dbo.substringBetween8K(@string,2,10,'.') UNION ALL
SELECT '1, NULL',* FROM dbo.substringBetween8K(@string,1,NULL,'.') UNION ALL
SELECT '1, 1', * FROM dbo.substringBetween8K(@string,1,NULL,'.');
---------------------------------------------------------------------------------------
Revision History:
Rev 00 - 20160720 - Initial Creation - Alan Burstein
****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH
chars AS
(
SELECT instance = 0, position = 0 WHERE @start = 0
UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY position), position
FROM dbo.NGrams8k(@string,1)
WHERE token = @delimiter
UNION ALL
SELECT -1, DATALENGTH(@string)+1 WHERE @stop = 0
)
SELECT token =
SUBSTRING
(
@string,
MIN(position)+1,
NULLIF(MAX(position),MIN(position)) - MIN(position)-1
),
position = CAST(
CASE WHEN NULLIF(MAX(position),MIN(position)) - MIN(position)-1 > 0
THEN MIN(position)+1 END AS smallint),
tokenLength = CAST(NULLIF(MAX(position),MIN(position)) - MIN(position)-1 AS smallint)
FROM chars
WHERE instance IN (@start, NULLIF(@stop,0), -1);
Using SubstringBetween8K on your data:
SELECT pathx, token
FROM #t
CROSS APPLY substringBetween8K(pathx,1,3,'/')
Results:
pathx token
-------------------------------------------------------------- -------------------------------
/CaseRateAnalysis/ NULL
/MedicaidBV250/ NULL
/Midwest Advantage/ NULL
/MidwestHealthPlan/Authorization/ MidwestHealthPlan/Authorization
/MidwestHealthPlan/Authorization/IT/ MidwestHealthPlan/Authorization
/MidwestHealthPlan/Claims/Archive Report/ MidwestHealthPlan/Claims
/MidwestHealthPlan/Claims/Archive Report/ClaimsAfterTerm/ MidwestHealthPlan/Claims
-- Itzik Ben-Gan 2001
September 26, 2016 at 1:07 pm
Luis Cazares (9/26/2016)
Different option:
SELECT '/' + LEFT( newpathx, CHARINDEX( '/', newpathx, CHARINDEX('/', newpathx) + 1))
FROM #t
CROSS APPLY( SELECT STUFF( pathx, 1, CHARINDEX('/', pathx), '') newpathx) x
WHERE pathx LIKE '%/%/%/%';
YAA (Yet Another Alternative 😀 )
😎
SELECT
X.OUTSTR
FROM #t TT
CROSS APPLY
(
SELECT STUFF(TT.pathx,( 1 + CHARINDEX('/',TT.pathx,CHARINDEX('/',TT.pathx,2) + 1)),8000,'')
) AS X(OUTSTR)
WHERE LEN(X.OUTSTR) > 2
GROUP BY X.OUTSTR;
September 26, 2016 at 1:39 pm
cool
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply