Aamir Aamir - 11 days ago 7
SQL Question

Get Server Name from server path from sql table column

I want to get server name from string inside column.

ex

CREATE TABLE #Sample(ServerPath varchar(50))
GO
INSERT INTO #Sample (ServerPath) VALUES ('\\TestServer1\Test')
INSERT INTO #Sample (ServerPath) VALUES ('\\TestServer2\Test')
INSERT INTO #Sample (ServerPath) VALUES ('\\TestServer3\Test')
INSERT INTO #Sample (ServerPath) VALUES ('TestServer4)
SELECT * FROM #Sample
GO
DROP TABLE #Sample`enter code here`


I want output like string in between double slash to first single slash

TestServer1
TestServer2
TestServer3


what sql command to execute or regular expression in sql which give me expected result.

Answer

This doesn't need to be regex (as per your tag). Just use something like this.

CREATE TABLE #Sample(ServerPath varchar(50))
INSERT INTO #Sample (ServerPath) 
VALUES 
('\\TestServer1\Test')
,('\\TestServer2\Test')
,('\\TestServer3\Test')

SELECT 
SUBSTRING(ServerPath,3,CHARINDEX('\',ServerPath,3)-3) Result
FROM #Sample 

Result
TestServer1
TestServer2
TestServer3

This is assuming that it always starts with double backslash

If you've got data without backslashes then use this to check first;

CREATE TABLE #Sample(ServerPath varchar(50))
INSERT INTO #Sample (ServerPath) 
VALUES 
('\\TestServer1\Test')
,('\\TestServer2\Test')
,('\\TestServer3\Test')
,('TestServer4')

SELECT 
CASE
   WHEN CHARINDEX('\\',ServerPath) = 0
      THEN ServerPath
   ELSE SUBSTRING(ServerPath,3,CHARINDEX('\',ServerPath,3)-3)
END Result
FROM #Sample 
GO
DROP TABLE #Sample

Result
TestServer1
TestServer2
TestServer3
TestServer4
Comments