CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
Cove
Cove

Reputation: 709

Regular expressions in TSQL

In cells of column e_vis_name I have organization structure where divisions divided with \ symbol, e.g.

I need to cut everything after first \ symbol to get the following result:

How can I do it?

Upvotes: 2

Views: 105

Answers (3)

shA.t
shA.t

Reputation: 16978

As some alternatives:

Using LEFT :

REPLACE(LEFT(e_vis_name, CHARINDEX('\', e_vis_name + '\', 1)), '\', '')

Using SUBSTRING :

REPLACE(SUBSTRING(e_vis_name, 1, CHARINDEX('\', e_vis_name + '\', 1)), '\', '')

Using STUFF :

STUFF(e_vis_name + '\', CHARINDEX('\', e_vis_name + '\', 1), 512, '')

Using PARSENAME :

REVERSE(PARSENAME(REVERSE(REPLACE(e_vis_name, '\','.')), 1))

-- or REPLACE(REVERSE(PARSENAME(REPLACE(REVERSE(REPLACE(e_vis_name, '.', CHAR(8))), '\','.'), 1)), CHAR(8), '.')

or 

PARSENAME(REPLACE(e_vis_name, '\','.'), LEN(e_vis_name) - LEN(REPLACE(e_vis_name, '\', '')) + 1)
-- or REPLACE(PARSENAME(REPLACE(REPLACE(e_vis_name, '.', CHAR(8)), '\','.'), LEN(e_vis_name) - LEN(REPLACE(e_vis_name, '\', '')) + 1) , CHAR(8), '.')

Upvotes: 1

Vasily
Vasily

Reputation: 5782

there are many options how you can achieve what you need, below several examples of them

--------------------------------------------------------------------------------
-- TEMP TABLE WITH DATA SAMPLE
DECLARE @table AS TABLE ( Division VARCHAR(100) )

INSERT INTO @table ( Division                       )
VALUES             ( 'Moscow\Direction'             )
,                  ( 'Yaroslavl\Sales'              )
,                  ( 'Omsk\Commercial center\Sales' )
,                  ( 'Voronezh'                     )

--------------------------------------------------------------------------------
-- variant using PARSENAME
SELECT REVERSE(PARSENAME(REVERSE(REPLACE(Division, '\', '.')), 1)) AS Town
FROM @table AS T

-------------------------------------------------------------------------------
-- variant using SUBSTRING AND CHARINDEX
SELECT  SUBSTRING(division, 1,
                  CASE WHEN CHARINDEX('\', division) = 0 THEN LEN(Division)
                       ELSE CHARINDEX('\', division) - 1
                  END) AS Town
FROM    @table AS T

--------------------------------------------------------------------------------
-- variant using SUBSTRING AND PATINDEX
SELECT  SUBSTRING(division, 1,
                  CASE WHEN PATINDEX('%\%', division) = 0 THEN LEN(Division)
                       ELSE PATINDEX('%\%', division) - 1
                  END) AS Town
FROM    @table AS T

--------------------------------------------------------------------------------
-- variant using LEFT AND PATINDEX

SELECT  LEFT(division,
             CASE WHEN PATINDEX('%\%', division) = 0 THEN LEN(Division)
                  ELSE PATINDEX('%\%', division) - 1
             END) AS Town
FROM    @table AS T

--------------------------------------------------------------------------------
-- variant using LEFT AND CHARINDEX
SELECT  LEFT(division,
             CASE WHEN CHARINDEX('\', division) = 0 THEN LEN(Division)
                  ELSE CHARINDEX('\', division) - 1
             END) AS Town
FROM    @table AS T

--------------------------------------------------------------------------------
-- variant using recursive cte, substring, top with ties by Row_number()
;
WITH tally
AS (SELECT n = 1
    UNION ALL
    SELECT n = n + 1
    FROM tally
    WHERE n < 100)

SELECT TOP 1 WITH TIES SUBSTRING(A.Division,1,B.n-1) AS Town
FROM @table AS A
JOIN tally  AS B ON SUBSTRING(A.Division + '\', B.n , 1)= '\'
ORDER BY ROW_NUMBER() OVER (PARTITION BY A.Division ORDER BY B.n)

--------------------------------------------------------------------------------
-- variant using recursive cte, substring, row_number, subquery
;
WITH tally
AS (SELECT n = 1
    UNION ALL
    SELECT n = n + 1
    FROM tally
    WHERE n < 100)

SELECT T.TOWN
FROM (SELECT SUBSTRING(A.Division,1,B.n-1) AS TOWN,
             ROW_NUMBER() OVER (PARTITION BY A.Division ORDER BY B.n) AS RN
      FROM @table AS A JOIN tally  AS B ON SUBSTRING(A.Division + '\', B.n , 1)= '\'
      ) AS T
WHERE RN = 1

Upvotes: 1

Tom Chantler
Tom Chantler

Reputation: 14951

You can use a combination of LEFT and CHARINDEX like this:

SELECT LEFT(colname, CHARINDEX('\', colname)-1) FROM table

EDIT: In the case where you don't have a \ symbol, if you just want to grab the whole column instead you can do this:

SELECT 
    CASE WHEN CHARINDEX('\', colname) > 0 THEN LEFT(colname, CHARINDEX('\', colname)-1)
    ELSE ISNULL(colname, '') 
    END
FROM table

This says, "If there is a \, then take the characters up to that point, otherwise take the whole column. And if the column is NULL then just set an empty string."

I'm sure you can adapt this to your purposes.

Upvotes: 5

Related Questions