2

I'm struggling to find the right functions with SQL Server 2008 to rectify any strings coming like: \\myserver\mydir1\dir2\test.txt or \\myserver2\dir1\dir2\dir3\test.txt.

At the end, the result should appear like: \\myserver\mydir1\dir2 or \\myserver2\dir1\dir2\dir3 , So after the last "\" , the substring should be removed.

Any idea how to do that?

Sai Kalyan Kumar Akshinthala
  • 11,704
  • 8
  • 43
  • 67
largo68
  • 609
  • 2
  • 16
  • 29

4 Answers4

3
DECLARE @urls TABLE (URL varchar(2000))

INSERT @urls VALUES ('\\myserver\mydir1\dir2\test.txt')
INSERT @urls VALUES ('\\myserver2\dir1\dir2\dir3\test.txt')

SELECT
    REVERSE(SUBSTRING(REVERSE(URL), CHARINDEX('\', REVERSE(URL))+1, 8000))
FROM
    @urls

Logic:

  • \\myserver\mydir1\dir2\test.txt
  • REVERSE = txt.tset\2rid\1ridym\revresym\
  • look for first \
  • take everything after that SUBSTRING = 2rid\1ridym\revresym\
  • REVERSE = \\myserver\mydir1\dir2

You don't need to know the LEN of the string for the SUBSTRING so just use 8000

Edit, after comment about using 8000

  1. You can use 2147483647 in SUBSTRING because it supports max types
  2. Only has to be equal to or longer that the varchar variable or column length
  3. What about the overhead of calculating LEN?
  4. Practically all URLs have to fit into 2083 bytes
  5. Does it matter? "Premature optimisation" etc
Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • @largo68: While just using 8000 may work, it's not scalable and wastes cycles trimming the string. If the string were longer than 8000, the database would still return, but the data would be invalid. Leading to a game of cat and mouse down the road between database developers and application developers. – Jeffrey Kevin Pry Jun 24 '11 at 12:41
  • @largo68: I understand your point... I've just inherited too many problems when programming made by short-sighted decisions when the correct way to do it is just as easy. – Jeffrey Kevin Pry Jun 24 '11 at 13:37
0
DECLARE @input VARCHAR(50)
SET @input = '\\myserver\mydir1\dir2\test.txt'

SELECT SUBSTRING(@input, 1, LEN(@input) - CHARINDEX('\', REVERSE(@input)));
trydis
  • 3,905
  • 1
  • 26
  • 31
0
DECLARE @s VarChar(100) = '\myserver\mydir1\dir2\test.txt';

DECLARE @length Int = LEN(@s),
        @position Int = CHARINDEX('\', REVERSE(@s));

SELECT SUBSTRING(@s, 1, @length - @position);
Yuck
  • 49,664
  • 13
  • 105
  • 135
0

Try something like this...

SELECT CASE SUBSTRING ( [yourField], LEN([yourField])-2, LEN([yourField])-1)
WHEN '\' THEN (SUBSTRING ( [yourField], 0, LEN([yourField])-1))
ELSE [yourField]

End
  FROM [yourTable]
GO

Hope that helps,

Jeffrey Kevin Pry

Jeffrey Kevin Pry
  • 3,266
  • 3
  • 35
  • 67