UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Substring, SQL Server 2012    
 
   
sorbit
post Aug 3 2017, 02:00 PM
Post#1



Posts: 642
Joined: 18-October 05
From: Roselle, Illinois


Good Afternoon,

I have a field where I need to extract the substring between the 4th and 5th "\" in the entire string. The entire string is a file path to an imagefile location, and the name of the pdf type is always between the 4th and 5th "\". If anyone has a solution to this I would very much appreciate your input.

Thanks,

Sorbit-

--------------------
The artist is nothing without the gift, but the gift is nothing without the work.
Go to the top of the page
 
MadPiet
post Aug 3 2017, 02:15 PM
Post#2



Posts: 2,100
Joined: 27-February 09



I'm using Jeff Moden's splitter (DelimitedSplit8K)

CODE
use tempdb;
GO

DECLARE @MyString VARCHAR(100) = 'abc\defg\hijkl\mn\opq\rst\uv\w\x\y\z'

SELECT Item
FROM Scratchpad.dbo.DelimitedSplit8K(@MyString,'\')
WHERE ItemNumber = 4;

Jeff's article is here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Yes, you have to create an account, but you can shut off all notifications from the site in your profile. And there are a LOT of scary smart SQL Server folks there.
This post has been edited by MadPiet: Aug 3 2017, 02:15 PM
Go to the top of the page
 
kfield7
post Aug 3 2017, 02:59 PM
Post#3



Posts: 726
Joined: 12-November 03
From: Conroe, TX


Use the split() function and retrieve the fifth element in the resulting array.
Go to the top of the page
 
MadPiet
post Aug 3 2017, 03:37 PM
Post#4



Posts: 2,100
Joined: 27-February 09



SQL Server 2012 doesn't have a built-in SPLIT function.... for some odd reason. Well, not til 2016
Go to the top of the page
 
sorbit
post Aug 3 2017, 04:24 PM
Post#5



Posts: 642
Joined: 18-October 05
From: Roselle, Illinois


This works just fine when I use your example. In this case 'abc\defg\hijkl\mn\opq\rst\uv\w\x\y\z' returns mn

When I substitute the result set that I need: IN (SELECT ImageFile FROM vw_MyStuff)

I get the following error:

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

--------------------
The artist is nothing without the gift, but the gift is nothing without the work.
Go to the top of the page
 
MadPiet
post Aug 3 2017, 07:36 PM
Post#6



Posts: 2,100
Joined: 27-February 09



Could you post the following:
1. CREATE TABLE script(s),
2. INSERT scripts to populate the table (sample/dummy data is fine, as long as it's representative).

I think I know what output you want, but if that's not already clear, post that too.
Go to the top of the page
 
sorbit
post Aug 4 2017, 07:12 AM
Post#7



Posts: 642
Joined: 18-October 05
From: Roselle, Illinois


Sure, I've attached some sample data. And here's the way I'm attempting to parse the ImageFile:

GO

DECLARE @MyString VARCHAR(255) = (SELECT IMAGEFILE FROM vw_IR WHERE ProjectId = 9999)

SELECT Item
FROM DelimitedSplit8K(@MyString,'\')
WHERE ItemNumber = 5;

In the attached, I'm trying to return: TET_Air_SRF for each of the 10 IsolateIds included.
Attached File(s)
Attached File  UA_Sample.zip ( 6.22K )Number of downloads: 1
 

--------------------
The artist is nothing without the gift, but the gift is nothing without the work.
Go to the top of the page
 
MadPiet
post Aug 4 2017, 08:18 AM
Post#8



Posts: 2,100
Joined: 27-February 09



CODE
use tempdb;
go

DECLARE @InputString VARCHAR(100) = 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\ADRGEN0000.tif';

SELECT Item
FROM Scratchpad.dbo.DelimitedSplit8K(@InputString,'\')
WHERE ItemNumber = 6;


Sounds like you should read Jeff's article explaining how DelimitedSplit8K works...
Go to the top of the page
 
sorbit
post Aug 4 2017, 09:24 AM
Post#9



Posts: 642
Joined: 18-October 05
From: Roselle, Illinois


Hi MadPiet,

Could you please be more specific? I am curious as to whether reading the article is going to help me find an alternative way to use the code, that works for me, or whether reading the article will make me realize that this solution isn't going to work for me.

Thanks in advance,

Sorbit-

--------------------
The artist is nothing without the gift, but the gift is nothing without the work.
Go to the top of the page
 
sorbit
post Aug 4 2017, 09:46 AM
Post#10



Posts: 642
Joined: 18-October 05
From: Roselle, Illinois


Just to be clear. This is simply a matter of curiosity. I could easily UNION up a bunch of SELECT TOP 1 * by project, export to excel, do a text to columns using \ as my delimiter, and then use those results to build my lookup table. It seemed like an interesting puzzle, so I decided to explore it. I'm perfectly content to go with the 'less elegant' solution if that proves to be the path of least resistance and/or SQL Server simply isn't powerful enough to provide a simple solution.

--------------------
The artist is nothing without the gift, but the gift is nothing without the work.
Go to the top of the page
 
MadPiet
post Aug 4 2017, 10:18 AM
Post#11



Posts: 2,100
Joined: 27-February 09



"It's a poor craftsman that blames his tools."

It works fine. You just dont know how to use it.

CREATE TABLE test(
IsoID INT,
ProjectID INT DEFAULT 9999,
ImageFile VARCHAR(100)
);
GO

INSERT INTO test(IsoID, ProjectID, ImageFile) VALUES
(3673565, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\ADRGEN0000.tif'),
(3673566, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\7KNKPC0000.tif'),
(3673567, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\ZZV6UT0000.tif'),
(3673568, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\7LB2OS0000.tif'),
(3673569, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\VEXR6Y0000.tif'),
(3673570, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\4VO2RU0000.tif'),
(3673571, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\37HMBZ0000.tif'),
(3673572, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\NJNPIJ0000.tif'),
(3673573, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\RUPQWS0000.tif'),
(3673574, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\SKRK8P0000.tif');

SELECT t.IsoID
,t.ImageFile
,ca.Item
,ca.ItemNumber
FROM test t CROSS APPLY Scratchpad.dbo.DelimitedSplit8K(t.ImageFile,'\') ca
WHERE ca.ItemNumber = 6;
Go to the top of the page
 
MadPiet
post Aug 4 2017, 10:18 AM
Post#12



Posts: 2,100
Joined: 27-February 09



"It's a poor craftsman that blames his tools."

It works fine. You just dont know how to use it.

CREATE TABLE test(
IsoID INT,
ProjectID INT DEFAULT 9999,
ImageFile VARCHAR(100)
);
GO

INSERT INTO test(IsoID, ProjectID, ImageFile) VALUES
(3673565, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\ADRGEN0000.tif'),
(3673566, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\7KNKPC0000.tif'),
(3673567, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\ZZV6UT0000.tif'),
(3673568, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\7LB2OS0000.tif'),
(3673569, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\VEXR6Y0000.tif'),
(3673570, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\4VO2RU0000.tif'),
(3673571, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\37HMBZ0000.tif'),
(3673572, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\NJNPIJ0000.tif'),
(3673573, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\RUPQWS0000.tif'),
(3673574, 9999, 'O:\AAY_TET\18 TET Year 1\Data\Images\TET_Air_SRF\00039811\SKRK8P0000.tif');

SELECT t.IsoID
,t.ImageFile
,ca.Item
,ca.ItemNumber
FROM test t CROSS APPLY Scratchpad.dbo.DelimitedSplit8K(t.ImageFile,'\') ca
WHERE ca.ItemNumber = 6;
Go to the top of the page
 
sorbit
post Aug 4 2017, 10:52 AM
Post#13



Posts: 642
Joined: 18-October 05
From: Roselle, Illinois


If your solution weren't so clumsy, I'd probably be offended by your condescension. But since a more capable craftsmen provided a much better, simpler, more elegent tool, I will just share that solution so that others who might be following this thread don't fall into the trap of using a hammer where a tidy little screwdriver gets the job done more effectively:

SELECT DISTINCT
SUBSTRING(IMAGEFILE, CHARINDEX('\', IMAGEFILE, CHARINDEX('\', IMAGEFILE, CHARINDEX('\', IMAGEFILE, CHARINDEX('\', IMAGEFILE, CHARINDEX('\', IMAGEFILE, 0) + 1) + 1) + 1) + 1) + 1,
CHARINDEX('\', IMAGEFILE, CHARINDEX('\', IMAGEFILE, CHARINDEX('\', IMAGEFILE, CHARINDEX('\', IMAGEFILE, CHARINDEX('\', IMAGEFILE, CHARINDEX('\', IMAGEFILE, 0) + 1) + 1) + 1) + 1) + 1) -
CHARINDEX('\', IMAGEFILE, CHARINDEX('\', IMAGEFILE, CHARINDEX('\', IMAGEFILE, CHARINDEX('\', IMAGEFILE, CHARINDEX('\', IMAGEFILE, 0) + 1) + 1) + 1) + 1) - 1) AS MyString
FROM vw_IR
where s_ProjectId = 9999

This will return a unique set of values between the two delimiters; avoiding the clumsy step of first identifying all the values.

Best,

Sorbit-

--------------------
The artist is nothing without the gift, but the gift is nothing without the work.
Go to the top of the page
 
MadPiet
post Aug 4 2017, 11:13 AM
Post#14



Posts: 2,100
Joined: 27-February 09



You would use DelimitedSplit8K if you didn't know how many "chunks" there were to split out, or if the position of the delimiters changed all the time.

"Clumsy"... hmm... Ask Jeff Moden, the guy who wrote the code. He's been a SQL Server MVP for like 10 years now... Could be more, I'm not sure. But feel free to discuss the finer points with him. If you click on the links in the article, you can ask him yourself.

Split wasn't added to SQL Server until 2016. Go argue with Microsoft about that.
Go to the top of the page
 
sorbit
post Aug 4 2017, 02:20 PM
Post#15



Posts: 642
Joined: 18-October 05
From: Roselle, Illinois


"Foolish is the individual that does not understand that context is king"

Should I also ask Jeff which type of wine goes best with this function? A Syrah or a Pinot Noir? I imagine this function is extremely elegant when suggested in the proper context. In this context, for this problem, it's a bad suggestion. A suggestion that you offered, and then chose to be sardonic at best and petty at worst once it was realized that your offering was a poor one. <Split wasn't added to SQL Server until 2016. Go argue with Microsoft about that.> Another bad suggestion! Hey, at least your batting .1000 in offering up bad ideas...

--------------------
The artist is nothing without the gift, but the gift is nothing without the work.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd August 2017 - 11:56 PM