June 7, 2016 at 6:50 am
Hi
Today i am facing one interview Questions
I have following folder structure(like string path)
1) c:\Programmefiles\Dicument\file1.txt
2) c:\Programmefiles\Dicument\Subdocument\file1.txt
3) c:\Programmefiles\Dicument\Image\file1.txt
4) c:\Programmefiles\Dicument\Text\file1.txt
I want to following output (I want last folder name)
1) Document
2)Subdocument
3)Image
4)Text
Please help me how to achieve this
June 7, 2016 at 7:09 am
What have you got so far and where are you stuck?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 7, 2016 at 7:25 am
Try this:
SELECT sys.fn_get_folder(path, 1)
June 7, 2016 at 7:44 am
Luis Cazares (6/7/2016)
Try this:
SELECT sys.fn_get_folder(path, 1)
jerking my chain or is that new in 2016?
i tried 2008/2012/2014 and below with no luck.
;WITH MyCTE([path])
AS
(
SELECT 'c:\Programmefiles\Dicument\file1.txt' UNION ALL
SELECT 'c:\Programmefiles\Dicument\Subdocument\file1.txt' UNION ALL
SELECT 'c:\Programmefiles\Dicument\Image\file1.txt' UNION ALL
SELECT 'c:\Programmefiles\Dicument\Text\file1.txt'
)
SELECT sys.fn_get_folder([path], 1),* FROM MyCTE;
Lowell
June 7, 2016 at 7:59 am
Thank you for ur reply
What is sys.fn_get_folder? Is it system Function ? Which version?
June 7, 2016 at 8:02 am
vs.satheesh (6/7/2016)
Thank you for ur replyWhat is sys.fn_get_folder? Is it system Function ? Which version?
I don't know this function, but I know REVERSE(), CHARINDEX() and PATINDEX(), and so should you. Here's a hint: if you reverse the word, then the piece you want is always between the first and second backslash.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 7, 2016 at 8:09 am
They're asking the wrong type of questions for a job interview.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 7, 2016 at 8:14 am
Luis Cazares (6/7/2016)
Try this:
SELECT sys.fn_get_folder(path, 1)
LOL that is hilarious Luis. And it seems to have fooled Lowell and Chris too. That is too funny.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 β Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2016 at 8:24 am
Was I too late for April fools' day?
This is what happens when you trust a forum to answer questions that you should answer on your own.
I could have given a real answer, but this is an example of the reasons behind my disclaimer. I wouldn't want someone that uses something without at least understanding how it works or at least point to some documentation for it.
If I was interviewing this person, I wouldn't mind if the answer is incorrect as long as it demonstrates knowledge on string manipulation and creativity to solve problems.
June 7, 2016 at 8:45 am
Eric M Russell (6/7/2016)
They're asking the wrong type of questions for a job interview.
I think it's a great question. It shows whether or not you actually know much about string manipulation. It's not a difficult question and, once I know if someone can actually get the current date and time (something like 80% have failed that simple question), then I move on to things like this. Both DBAs and Developers need to know simple stuff like this.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2016 at 8:46 am
Luis Cazares (6/7/2016)
Try this:
SELECT sys.fn_get_folder(path, 1)
Absolutely the correct answer! π Well, except for the Hungarian notation used. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2016 at 8:48 am
Eric M Russell (6/7/2016)
They're asking the wrong type of questions for a job interview.
Yup. But it would be a good homework after a training session on string functions.
June 7, 2016 at 8:49 am
vs.satheesh (6/7/2016)
HiToday i am facing one interview Questions
I have following folder structure(like string path)
1) c:\Programmefiles\Dicument\file1.txt
2) c:\Programmefiles\Dicument\Subdocument\file1.txt
3) c:\Programmefiles\Dicument\Image\file1.txt
4) c:\Programmefiles\Dicument\Text\file1.txt
I want to following output (I want last folder name)
1) Document
2)Subdocument
3)Image
4)Text
Please help me how to achieve this
You can science this out. As a human, what do you do? You start from the right and find the first backslash from the right. Ask your self how you would use CHARINDEX to search from the right. Remember that searching from the right is the REVERSE of what you'd normally do.
I'd give you the answer but then you might not learn as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2016 at 8:50 am
Jeff Moden (6/7/2016)
Luis Cazares (6/7/2016)
Try this:
SELECT sys.fn_get_folder(path, 1)
Absolutely the correct answer! π Well, except for the Hungarian notation used. :hehe:
That's the MS standard. π
June 7, 2016 at 8:53 am
Sean Lange (6/7/2016)
Luis Cazares (6/7/2016)
Try this:
SELECT sys.fn_get_folder(path, 1)
LOL that is hilarious Luis. And it seems to have fooled Lowell and Chris too. That is too funny.
Haven't you got one of these Sean?
ALTER FUNCTION dbo.fn_get_folder
(@path VARCHAR(255), @Element TINYINT)
RETURNS TABLE
AS
RETURN (
SELECT Element = NULLIF(SUBSTRING(@path, MIN(n)+1, MAX(n)-MIN(n)),'')
FROM (
SELECT
n, Item = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (
SELECT n = 0
UNION ALL
SELECT n
FROM (
SELECT TOP(LEN(@path)) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t1 (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t2 (n)
) _tally
WHERE SUBSTRING(@path,n,1) = '\'
UNION ALL
SELECT n = LEN(@path)
) p
) q
WHERE Item = @Element OR Item = @Element+1
)
GO
SELECT Element FROM dbo.fn_get_folder ('c:\Programmefiles\Dicument\file1.txt',1)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 111 total)
You must be logged in to reply to this topic. Login to reply