April 7, 2017 at 7:09 am
Hi,
Im trying to get all the text between the third backslash and fourth backslash based on a select query - can anyone help me please? The results may not have a folder structure which goes into the 3rd or 4th folder level but obviously these would be ignored based on the search structure.
Current code is
SELECT DISTINCT path
FROM [DB].[dbo].[Table]
Where [Path] like '\Enq%'
AND [Deleted] = '0'
Current results are
\ENQ123\Testing Data\Testing More Data\Test
\ENQ345\Testing Data2\Testing More Data1234\Test 123\Testing
\ENQ456\Testing Data3\Testing More Data45678\Test ABCD\Testing 1234456
April 7, 2017 at 7:22 am
Using the DelimitedSplit8K function:WITH Paths AS (
SELECT *
FROM (VALUES
('\ENQ123\Testing Data\Testing More Data\Test'),
('\ENQ345\Testing Data2\Testing More Data1234\Test 123\Testing'),
('\ENQ456\Testing Data3\Testing More Data45678\Test ABCD\Testing 1234456')
) AS P (FilePath)
)
SELECT *
FROM Paths P
CROSS APPLY dbo.DelimitedSplit8K (P.FilePath, '\') DS
WHERE DS.ItemNumber = 4;
This should put you in the right path for your own query.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 7, 2017 at 9:28 am
Thom A - Friday, April 7, 2017 7:22 AMUsing the DelimitedSplit8K function:WITH Paths AS (
SELECT *
FROM (VALUES
('\ENQ123\Testing Data\Testing More Data\Test'),
('\ENQ345\Testing Data2\Testing More Data1234\Test 123\Testing'),
('\ENQ456\Testing Data3\Testing More Data45678\Test ABCD\Testing 1234456')
) AS P (FilePath)
)
SELECT *
FROM Paths P
CROSS APPLY dbo.DelimitedSplit8K (P.FilePath, '\') DS
WHERE DS.ItemNumber = 4;
This should put you in the right path for your own query.
Hi,
Thanks for the response but and excuse my ignorance, the results I gave as an example were the results of my current query. The records in the Paths table don't always start ENQXXX that's why I initially did a search so I only get the ENQXXX records. Am I correct in thinking your example thinks that the values are always '\ENQ123XXXXX......
Thanks
April 7, 2017 at 9:35 am
Shabbaranks - Friday, April 7, 2017 9:28 AMThom A - Friday, April 7, 2017 7:22 AMUsing the DelimitedSplit8K function:WITH Paths AS (
SELECT *
FROM (VALUES
('\ENQ123\Testing Data\Testing More Data\Test'),
('\ENQ345\Testing Data2\Testing More Data1234\Test 123\Testing'),
('\ENQ456\Testing Data3\Testing More Data45678\Test ABCD\Testing 1234456')
) AS P (FilePath)
)
SELECT *
FROM Paths P
CROSS APPLY dbo.DelimitedSplit8K (P.FilePath, '\') DS
WHERE DS.ItemNumber = 4;
This should put you in the right path for your own query.Hi,
Thanks for the response but and excuse my ignorance, the results I gave as an example were the results of my current query. The records in the Paths table don't always start ENQXXX that's why I initially did a search so I only get the ENQXXX records. Am I correct in thinking your example thinks that the values are always '\ENQ123XXXXX......
Thanks
The statement I provided used your sample data, as I don't have access to your data, and as they all started with ENQ and I didn't have a column Deleted I excluded them from my WHERE clause. The main part you need is the final SELECT statement, which has the logic you need. you would need to apply this to your own source table, and add any additional requirements into your WHERE clause(i.e. AND [Deleted] = '0').
Try updating your SELECT statement yourself first, it's a good way of learning, rather than having someone do it for you. If you get stuck, post back showing what you tried and I, or another user, will show you where you went wrong 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 10, 2017 at 9:32 am
Thom A - Friday, April 7, 2017 9:35 AMShabbaranks - Friday, April 7, 2017 9:28 AMThom A - Friday, April 7, 2017 7:22 AMUsing the DelimitedSplit8K function:WITH Paths AS (
SELECT *
FROM (VALUES
('\ENQ123\Testing Data\Testing More Data\Test'),
('\ENQ345\Testing Data2\Testing More Data1234\Test 123\Testing'),
('\ENQ456\Testing Data3\Testing More Data45678\Test ABCD\Testing 1234456')
) AS P (FilePath)
)
SELECT *
FROM Paths P
CROSS APPLY dbo.DelimitedSplit8K (P.FilePath, '\') DS
WHERE DS.ItemNumber = 4;
This should put you in the right path for your own query.Hi,
Thanks for the response but and excuse my ignorance, the results I gave as an example were the results of my current query. The records in the Paths table don't always start ENQXXX that's why I initially did a search so I only get the ENQXXX records. Am I correct in thinking your example thinks that the values are always '\ENQ123XXXXX......
ThanksThe statement I provided used your sample data, as I don't have access to your data, and as they all started with ENQ and I didn't have a column Deleted I excluded them from my WHERE clause. The main part you need is the final SELECT statement, which has the logic you need. you would need to apply this to your own source table, and add any additional requirements into your WHERE clause(i.e. AND [Deleted] = '0').
Try updating your SELECT statement yourself first, it's a good way of learning, rather than having someone do it for you. If you get stuck, post back showing what you tried and I, or another user, will show you where you went wrong 🙂
Thanks for this - am I correct in thinking the bit after the second bracket is re-querying the results of the first query? If I use any commands such as Cross Apply I get the red underline - after some googling am I correct in thinking this cross apply is related to SQL 2005 and not 2014?
Thanks
April 10, 2017 at 9:44 am
Shabbaranks - Monday, April 10, 2017 9:32 AMThanks for this - am I correct in thinking the bit after the second bracket is re-querying the results of the first query? If I use any commands such as Cross Apply I get the red underline - after some googling am I correct in thinking this cross apply is related to SQL 2005 and not 2014?
Thanks
CROSS APPLY is very much current SQL. It was introduced in SQL 2005. It is not deprecated, and is not expected to be.
What is the SQL you are trying to perform if you are getting an incorrect syntax warning from intellisense?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 11, 2017 at 9:38 am
Actually there is no need for the expense a splitter function in this scenario...
IF OBJECT_ID('tempdb..#FolderPath', 'U') IS NOT NULL
DROP TABLE #FolderPath;
GO
CREATE TABLE #FolderPath (
FullPath VARCHAR(255) NOT NULL
);
INSERT #FolderPath (FullPath) VALUES
('\ENQ123\Testing Data\Testing More Data\Test'),
('\ENQ345\Testing Data2\Testing More Data1234\Test 123\Testing'),
('\ENQ456\Testing Data3\Testing More Data45678\Test ABCD\Testing 1234456');
GO
-- ==============================================================================
SELECT
fp.FullPath,
SubPathName = SUBSTRING(fp.FullPath, s3.S3_Pos, s4.S4_Pos - s3.S3_Pos)
FROM
#FolderPath fp
CROSS APPLY ( VALUES (CHARINDEX('\', fp.FullPath, 2)) ) s2 (S2_Pos)
CROSS APPLY ( VALUES (CHARINDEX('\', fp.FullPath, s2.S2_Pos + 1) + 1) ) s3 (S3_Pos)
CROSS APPLY ( VALUES (CHARINDEX('\', fp.FullPath, s3.S3_Pos + 1)) ) s4 (S4_Pos)
April 14, 2017 at 2:56 pm
I'm a little late here but I have a function that is designed for exactly this type of thing. If you grab a copy on NGrams8k you could create this "SubstringBetween" function:
CREATE FUNCTION dbo.substringBetween8K
(
@string varchar(8000),
@start tinyint,
@stop tinyint,
@delimiter char(1)
)
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);
Then you can use it like this:
-- Using Jason's sample data
IF OBJECT_ID('tempdb..#FolderPath', 'U') IS NOT NULL DROP TABLE #FolderPath;
CREATE TABLE #FolderPath (FullPath VARCHAR(255) NOT NULL);
GO
INSERT #FolderPath (FullPath) VALUES
('\ENQ123\Testing Data'),
('\ENQ123\Testing Data\Testing More Data\Test'),
('\ENQ345\Testing Data2\Testing More Data1234\Test 123\Testing'),
('\ENQ456\Testing Data3\Testing More Data45678\Test ABCD\Testing 1234456');
GO
SELECT FullPath, SubpathName = token
FROM #FolderPath
CROSS APPLY dbo.substringBetween8K(FullPath, 3, 4, '\');
This code will even return a null for cases where there aren't 3 or 4 backslashes.
-- Itzik Ben-Gan 2001
April 14, 2017 at 9:16 pm
Shabbaranks - Friday, April 7, 2017 9:28 AMThom A - Friday, April 7, 2017 7:22 AMUsing the DelimitedSplit8K function:WITH Paths AS (
SELECT *
FROM (VALUES
('\ENQ123\Testing Data\Testing More Data\Test'),
('\ENQ345\Testing Data2\Testing More Data1234\Test 123\Testing'),
('\ENQ456\Testing Data3\Testing More Data45678\Test ABCD\Testing 1234456')
) AS P (FilePath)
)
SELECT *
FROM Paths P
CROSS APPLY dbo.DelimitedSplit8K (P.FilePath, '\') DS
WHERE DS.ItemNumber = 4;
This should put you in the right path for your own query.Hi,
Thanks for the response but and excuse my ignorance, the results I gave as an example were the results of my current query. The records in the Paths table don't always start ENQXXX that's why I initially did a search so I only get the ENQXXX records. Am I correct in thinking your example thinks that the values are always '\ENQ123XXXXX......
Thanks
You might be misunderstanding. The CTE (the thing starting with "WITH" is just a way of including test data. It isn't actually meant to be a part of your query.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2017 at 7:30 am
Thanks for the input guys its really appreciated (all be it I am a little lost). My SQL knowledge is limited hence me being lost so please be patient with me 🙂
How do you implement a function within an SQL query? Also as above when you say "a way of including test data" the data I am after is within a result of an initial query which returns the whole path. Can you query a query to then reduce that to only show the info within the 3rd and fourth "\" backslash?
Thanks guys 🙂
April 27, 2017 at 12:40 pm
Shabbaranks - Thursday, April 27, 2017 7:30 AMThanks for the input guys its really appreciated (all be it I am a little lost). My SQL knowledge is limited hence me being lost so please be patient with me 🙂
How do you implement a function within an SQL query? Also as above when you say "a way of including test data" the data I am after is within a result of an initial query which returns the whole path. Can you query a query to then reduce that to only show the info within the 3rd and fourth "\" backslash?
Thanks guys 🙂
It would go something like this...
1) Create a few rows of test data... (temp table called #Paths)
IF OBJECT_ID('tempdb..#Paths', 'U') IS NOT NULL
DROP TABLE #Paths;
GO
CREATE TABLE #Paths (
FilePath VARCHAR(1000) NOT NULL
);
INSERT #Paths (FilePath) VALUES
('\ENQ123\Testing Data\Testing More Data\Test'),
('\ENQ345\Testing Data2\Testing More Data1234\Test 123\Testing'),
('\ENQ456\Testing Data3\Testing More Data45678\Test ABCD\Testing 1234456');
GO
2) Create a new inline table valued function (dbo.tfn_ThirdFilePathNode)
CREATE FUNCTION dbo.tfn_ThirdFilePathNode
/* =========================================================
04/27/2017 Created... Returns the 3rd node in a file path
========================================================= */
(
@FilePath VARCHAR(1000)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT
SubPathName = SUBSTRING(@FilePath, s3.S3_Pos, s4.S4_Pos - s3.S3_Pos)
FROM
( VALUES (CHARINDEX('\', @FilePath, 2)) ) s2 (S2_Pos)
CROSS APPLY ( VALUES (CHARINDEX('\', @FilePath, s2.S2_Pos + 1) + 1) ) s3 (S3_Pos)
CROSS APPLY ( VALUES (CHARINDEX('\', @FilePath, s3.S3_Pos + 1)) ) s4 (S4_Pos);
GO
3) Use the function against the test data created in step 1...
SELECT
p.FilePath,
tfpn.SubPathName
FROM
#Paths p
CROSS APPLY dbo.tfn_ThirdFilePathNode(p.FilePath) tfpn;
And finally, some results...
FilePath SubPathName
--------------------------------------------------------------------- ----------------------------
\ENQ123\Testing Data\Testing More Data\Test Testing More Data
\ENQ345\Testing Data2\Testing More Data1234\Test 123\Testing Testing More Data1234
\ENQ456\Testing Data3\Testing More Data45678\Test ABCD\Testing 1234456 Testing More Data45678
May 12, 2017 at 3:01 am
Using the solution provided by Jason A. Long.
If you are more comfortable with the 'WITH' construction than with the 'APPLY', the next very similar might be of some 'assistence'.
Investing in understanding the 'WITH' construction has been extremely valueble to me. (Without the possible recursive posibilities, the 'WITH' statement is 'easy' to understand and very usefull in loads of situations, especially in creating better readable and testeble code).
IF OBJECT_ID('tempdb..#FolderPath', 'U') IS NOT NULL
DROP TABLE #FolderPath;
GO
CREATE TABLE #FolderPath (
FullPath VARCHAR(255) NOT NULL
);
INSERT #FolderPath (FullPath) VALUES
('\ENQ123\Testing Data\Testing More Data\Test'),
('\ENQ345\Testing Data2\Testing More Data1234\Test 123\Testing'),
('\ENQ456\Testing Data3\Testing More Data45678\Test ABCD\Testing 1234456');
GO
-------------------------------------------------------------------------------------
--
-- Yet another variant solution, based on the solution of Jason A. Long.
--
;
WITH
A as (select *, CHARINDEX('\', FullPath, 2) S2_pos FROM #FolderPath)
, B as (select *, CHARINDEX('\', FullPath, S2_Pos + 1) + 1 S3_pos FROM A)
, C as (select *, CHARINDEX('\', FullPath, S3_Pos + 1) S4_pos FROM B)
, D as (select *, SUBSTRING(FullPath, S3_Pos, S4_Pos - S3_Pos) SubPathName FROM C)
SELECT * FROM D
-------------------------------------------------------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply