CODEWITHSUNDEEP
sql-serversql-server-2008
AskMe
AskMe

Reputation: 2583

Remove strings after special characters - SQL Server

My input Table is like this:

 PARSED_VALUE2        |   PARSED_VALUE4
 ---------------------|---------------
 CHIN     CONTACT     |  CHIN     CONTACT
 ---------------------|----------------
 HAMLET / PH          |  HAMLET / PH
----------------------|---------------
 NameM(               |  NameM(
----------------------|----------------

The output I want is like this:

       PARSED_VALUE   |   PARSED_VALUE4
 ---------------------|---------------
 CHIN        CONTACT  | CHIN 
 ---------------------|----------------
 HAMLET / PH          | HAMLET
----------------------|---------------
 NameM(               | NameM
----------------------|----------------

However, I'm getting the output as:

 The output I want is like this:

       PARSED_VALUE   |   PARSED_VALUE4
 ---------------------|---------------
 CHIN        CONTACT  | CHIN 
 ---------------------|----------------
 HAMLET / PH          | HAMLET / PH
----------------------|---------------
 NameM(               | NameM(
----------------------|----------------

This is the code I have written:

SELECT *
       , COALESCE(CASE WHEN PATINDEX('%' + '/' + '%',PARSED_VALUE2) >=1 
                       THEN left(PARSED_VALUE2,CHARINDEX('/', PARSED_VALUE2)-1) 
                       ELSE PARSED_VALUE2 END   --HAMLET / PH
       , CASE WHEN PATINDEX('%' + 'CONTACT' + '%',PARSED_VALUE2) >=1 
              THEN left(PARSED_VALUE2,CHARINDEX('CONTACT', PARSED_VALUE2)-1) 
              ELSE PARSED_VALUE2 END
       , CASE WHEN PATINDEX('%' + '(' + '%',PARSED_VALUE2) >=1 
              THEN left(PARSED_VALUE2,CHARINDEX('(', PARSED_VALUE2)-1) 
              ELSE PARSED_VALUE2 END) PARSED_VALUE4 
  FROM #TEMPP

However, I'm not getting the desired result. What is wrong with this query?

Upvotes: 0

Views: 64

Answers (2)

Chester Lim
Chester Lim

Reputation: 519

I suggest get the index of the first special character using PATINDEX, then use LEFT (index - 1) to get the data you need.

SELECT LEFT(PARSED_VALUE2, PATINDEX('%[^a-zA-Z0-9]%', PARSED_VALUE2) - 1)
FROM #TEMPP

Upvotes: 1

user7715598
user7715598

Reputation:

Try this

;WITH cte(PARSED_VALUE2)
AS
(
SELECT 'CHIN     CONTACT'UNION ALL
SELECT 'HAMLET / PH'     UNION ALL
SELECT 'NameM('          
)
SELECT PARSED_VALUE2,
      SUBSTRING(PARSED_VALUE2,0,PATINDEX('%[/( ]%',PARSED_VALUE2)) AS PARSED_VALUE4 
FROM cte

Result

PARSED_VALUE2       PARSED_VALUE4
-------------------------------------
CHIN     CONTACT    CHIN
HAMLET / PH         HAMLET
NameM(              NameM

Upvotes: 1

Related Questions