November 7, 2011 at 4:28 pm
Steve Kinnaman (11/7/2011)
Yes That work great. A friend suggested to replace DATALENGTH with LEN, and that worked as well.
I agree with TOM. I recommend you continue to use DATALENGTH for the very reason he stated.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 7, 2011 at 9:00 pm
Jeff Moden (11/7/2011)
I agree with TOM. I recommend you continue to use DATALENGTH for the very reason he stated.
Trouble is, that implementation now only works with Unicode data. If used on VARCHAR data, an error is produced because of the hard-coded factor of 2. There is an easy way to make the implementation work with both data types, but instead of modifying the existing routine, here's an alternative that also works on both data types, using LEN:
SELECT
LEFT(d.OFFERT, LastBackslash.position - 1),
RIGHT(d.OFFERT, LEN(d.OFFERT) - LastBackslash.position)
FROM dbo.Dokument$ AS d
CROSS APPLY
(
SELECT TOP (1)
v.number
FROM master.dbo.spt_values AS v
WHERE
v.[type] = N'P'
AND v.number BETWEEN 1 AND LEN(d.OFFERT)
AND SUBSTRING(d.OFFERT, v.number, 1) = '\' COLLATE Latin1_General_BIN2
ORDER BY
v.number DESC
) AS LastBackslash (position)
November 8, 2011 at 10:00 am
SQL Kiwi (11/7/2011)
Jeff Moden (11/7/2011)
I agree with TOM. I recommend you continue to use DATALENGTH for the very reason he stated.Trouble is, that implementation now only works with Unicode data. If used on VARCHAR data, an error is produced because of the hard-coded factor of 2. There is an easy way to make the implementation work with both data types, but instead of modifying the existing routine, here's an alternative that also works on both data types, using LEN:
I agree with Paul, ideally the code should work with both VARCHAR and NVARCHAR data, and I quite like his method. Using a projection of master.dbo.spt_values onto the number column as a smallish [0 to 2047] tally table is a nice idea, if a little adventurous as this table is, I believe, undocumented. But it might break down when using extended paths (of the form "\\?\D:\very long path") because there the path length can be as high as 32767, which is rather more than 2047 (anyone who has full file path plus file name coming to more than 2047 characters probably has more important problems than separating the path and the filename though, so that probably doesn't matter).
Tom
November 8, 2011 at 11:05 am
SQL Kiwi (11/7/2011)
Jeff Moden (11/7/2011)
I agree with TOM. I recommend you continue to use DATALENGTH for the very reason he stated.Trouble is, that implementation now only works with Unicode data. If used on VARCHAR data, an error is produced because of the hard-coded factor of 2. There is an easy way to make the implementation work with both data types, but instead of modifying the existing routine, here's an alternative that also works on both data types, using LEN:
SELECT
LEFT(d.OFFERT, LastBackslash.position - 1),
RIGHT(d.OFFERT, LEN(d.OFFERT) - LastBackslash.position)
FROM dbo.Dokument$ AS d
CROSS APPLY
(
SELECT TOP (1)
v.number
FROM master.dbo.spt_values AS v
WHERE
v.[type] = N'P'
AND v.number BETWEEN 1 AND LEN(d.OFFERT)
AND SUBSTRING(d.OFFERT, v.number, 1) = '\' COLLATE Latin1_General_BIN2
ORDER BY
v.number DESC
) AS LastBackslash (position)
Very cool idea... I've not done a deep dive on it yet so go easy on me but wouldn't this...
RIGHT(d.OFFERT, LEN(d.OFFERT) - LastBackslash.position)
Still cause trailing spaces in the filename to be missed>
--Jeff Moden
Change is inevitable... Change for the better is not.
November 8, 2011 at 12:36 pm
Jeff Moden (11/8/2011)
SQL Kiwi (11/7/2011)
Jeff Moden (11/7/2011)
I agree with TOM. I recommend you continue to use DATALENGTH for the very reason he stated.Trouble is, that implementation now only works with Unicode data. If used on VARCHAR data, an error is produced because of the hard-coded factor of 2. There is an easy way to make the implementation work with both data types, but instead of modifying the existing routine, here's an alternative that also works on both data types, using LEN:
SELECT
LEFT(d.OFFERT, LastBackslash.position - 1),
RIGHT(d.OFFERT, LEN(d.OFFERT) - LastBackslash.position)
FROM dbo.Dokument$ AS d
CROSS APPLY
(
SELECT TOP (1)
v.number
FROM master.dbo.spt_values AS v
WHERE
v.[type] = N'P'
AND v.number BETWEEN 1 AND LEN(d.OFFERT)
AND SUBSTRING(d.OFFERT, v.number, 1) = '\' COLLATE Latin1_General_BIN2
ORDER BY
v.number DESC
) AS LastBackslash (position)
Very cool idea... I've not done a deep dive on it yet so go easy on me but wouldn't this...
RIGHT(d.OFFERT, LEN(d.OFFERT) - LastBackslash.position)Still cause trailing spaces in the filename to be missed>
Yes, it does, but that's OK: the original solution using LEN causes the boundary between the path and the filename to be miscalculated if there are trailing spaces, so that for example 'c:\thing\fu.bar ' will be analysed as 'c:\thi' for path and 'g\fu.bar' for filename, and Paul's solution doesn't fall into that trap. Neither does the solution using datalength instead of len (and it does preserve the trailing spaces) but the killer is, as Paul points out, that the naive and simple way of writing it requires different code for the unicode and non-unicode cases.
As I believe that catering for both character formats (plus any future variants of unicode) should be catered for, and the trailing spaces on the filename are in any case an error and should be discarded, I much prefer Paul's method to the others that have been posted.
Tom
November 8, 2011 at 4:15 pm
Fair enough... then let's tweak Paul's bit of Tally-Table-like prestidigitation with a fix that will handle trailing spaces on the file name... 🙂
SELECT
LEFT(d.OFFERT, LastBackslash.position - 1),
[font="Arial Black"]SUBSTRING(@SomePath, LastBackslash.position+1, 2147483647)[/font]
FROM dbo.Dokument$ AS d
CROSS APPLY
(
SELECT TOP (1)
v.number
FROM master.dbo.spt_values AS v
WHERE
v.[type] = N'P'
AND v.number BETWEEN 1 AND LEN(d.OFFERT)
AND SUBSTRING(d.OFFERT, v.number, 1) = '\' COLLATE Latin1_General_BIN2
ORDER BY
v.number DESC
) AS LastBackslash (position)
--Jeff Moden
Change is inevitable... Change for the better is not.
November 8, 2011 at 7:10 pm
L' Eomot Inversé (11/8/2011)
Using a projection of master.dbo.spt_values onto the number column as a smallish [0 to 2047] tally table is a nice idea, if a little adventurous as this table is, I believe, undocumented. But it might break down when using extended paths (of the form "\\?\D:\very long path") because there the path length can be as high as 32767, which is rather more than 2047 (anyone who has full file path plus file name coming to more than 2047 characters probably has more important problems than separating the path and the filename though, so that probably doesn't matter).
Yes I really should stop using spt_values in demo code; it's pure laziness on my part. It is undocumented, and I have never used it in a production system for that reason. Undoubtedly handy for lazy demo code though. Anyway, here's a version that uses an on-the-fly numbers table, copes with VARCHAR and NVARCHAR, and preserves trailing space (for generality - it is pointless to do this on a filename):
WITH
N1 AS
(SELECT N1.n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N1 (n)),
N2 AS (SELECT L.n FROM N1 AS L CROSS JOIN N1 AS R),
N3 AS (SELECT L.n FROM N2 AS L CROSS JOIN N2 AS R),
N4 AS (SELECT L.n FROM N3 AS L CROSS JOIN N3 AS R),
N AS (SELECT ROW_NUMBER() OVER (ORDER BY @@SPID) AS n FROM N4)
SELECT
LEFT(d.OFFERT, LastBackslash.position - 1),
RIGHT(d.OFFERT, CharCount.n - LastBackslash.position)
FROM dbo.Dokument$ AS d
CROSS APPLY
(
SELECT DATALENGTH(d.OFFERT) / DATALENGTH(LEFT(d.OFFERT + SPACE(1), 1))
) AS CharCount (n)
CROSS APPLY
(
SELECT MAX(N.n)
FROM N
WHERE
N.n BETWEEN 1 AND CharCount.n
AND SUBSTRING(d.OFFERT, N.n, 1) = '\' COLLATE Latin1_General_BIN2
) AS LastBackslash (position)
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply