CODEWITHSUNDEEP
sqlsql-servert-sql
Alvie
Alvie

Reputation: 29

How do i get a certain string from a column in sql

Column name Subject

Value = Something TEST001 something

I need to get TEST001 from "Something TEST001 Something"

If there are spaces or any specials characters after the 1 of TEST001 they will be removed.

I only have this

SELECT   
    Subject
    , REPLACE(SUBSTRING(MailSubject, CHARINDEX('TEST', MailSubject), LEN(MailSubject)),'', '') AS Assingment
FROM    
    AssingmentEmail

The numbers from TEST001 can be more but if there are any spaces or non numeric it will be removed.

Upvotes: 1

Views: 106

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Based on comments here is edited solution:

DECLARE @t TABLE(v VARCHAR(100))
INSERT INTO @t VALUES
('Something TEST001 something'),
('Something [TEST0001] something'),
('Something TEST001 something 123 something'),
('Something {TEST0001} something 123 something')

;WITH cte AS(SELECT SUBSTRING(v, CHARINDEX('TEST', v), LEN(v)) AS v FROM @t)
SELECT SUBSTRING(v, 1, PATINDEX('%[^TEST0-9]%', v) - 1) AS v FROM cte

Output:

v
TEST001
TEST0001
TEST001
TEST0001

Explanation:

In cte you are selecting substrings:

TEST001 something
TEST0001] something
TEST001 something 123 something
TEST0001} something 123 something

Then you are searching for first occurrence of symbol that is not T E S T 0 1 2 3...9 with %[^TEST0-9]% and get substring till that symbol.

Upvotes: 1

Matt
Matt

Reputation: 15061

Use a combination of SUBSTRING, CHARINDEX & LEN. Also works regardless of how many digits are in the TESTXXXX.

SELECT MailSubject,
LEFT(SUBSTRING([MailSubject], CHARINDEX('TEST', [MailSubject]), LEN([MailSubject])), CHARINDEX(' ', SUBSTRING([MailSubject], 
CHARINDEX('TEST', [MailSubject]), LEN([MailSubject]))) - 1) AS [MailSubject]
FROM AssingmentEmail

INPUT:

MailSubject
something TEST001 something 
something TEST201 something
something TEST20122 something
something TEST601 something

OUTPUT:

MailSubject                      MailSubject
something TEST001 something      TEST001
something TEST201 something      TEST201
something TEST20122 something    TEST20122
something TEST601 something      TEST601

SQL FIDDLE: http://sqlfiddle.com/#!3/93660d/1/0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I would do this using a subquery. It is easier to handle the intermediate results. First, get rid of the part before the "TEST". Then, take the result up the first first space:

select left(ms1, charindex(' ', ms1)) as TestStuff
from (select stuff(MailSubject, 1, charindex(' TEST', MailSubject), '') as ms1
      from assignment email
     ) ae

Upvotes: 0

Related Questions