CODEWITHSUNDEEP
sqlsql-serversql-server-2008
goofyui
goofyui

Reputation: 3492

How to filter a string in SQL?

How to filter a string in SQL 2008?

SELECT FileName=reverse(left(reverse('\\PRODSERVER\D$\EXPORT\Data20160401.txt'), 

                    charindex('\',reverse('\\PRODSERVER\D$\EXPORT\Data20160401.txt'), 

                             1) - 1))

Above query returns the file name which is Data20160401.txt.

I need to fetch only the server name which is PRODSERVER.

Upvotes: 0

Views: 2544

Answers (3)

Jeremy C.
Jeremy C.

Reputation: 2465

Asuming the path of your file always starts with \\ you could do something like:

Filename=substring(string,0,charindex(substring(string,2,len(string)-2),'\')

Don't know if this is the exact correct syntax as I am not on a machine with any sql processor at the moment but it should do something like this:

  1. Get the substring of your string without the leading \\
  2. Get the location of the 3rd \ as that is where the part you dont need starts
  3. Grab the substring from the substring you created in 1 from first position after the \\ until the position of the 3rd \

Upvotes: 0

Bill Hurt
Bill Hurt

Reputation: 749

DECLARE @path  VARCHAR(50) = '\\PRODSERVER\D$\EXPORT\Data20160401.txt'

Select SubString(@path,3,(CHARINDEX('\',@path,3)-3))

Upvotes: 1

bastos.sergio
bastos.sergio

Reputation: 6764

Create a function to split your string

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Invoke the function

select *from dbo.fnSplitString('\\PRODSERVER\D$\EXPORT\Data20160401.txt','\')

Output

PRODSERVER
D$
EXPORT
Data20160401.txt

Upvotes: 1

Related Questions