CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
user10516497
user10516497

Reputation:

Extract string dynamically after a certain character SQL

I want to extract a certain phrase from a column after a/any symbol. For example, If I want to extract Tutorial from SQL-Tutorial, following query shall suffice.

SELECT SUBSTRING('SQL-Tutorial', 5, 8) AS ExtractString;

What if I want to extract everything before or after '-' or '_' from the following fields in a column?

Upvotes: 1

Views: 3458

Answers (3)

Chanukya
Chanukya

Reputation: 5893

CREATE TABLE #Table1
    ([COLN] varchar(24))
;

INSERT INTO #Table1
    ([COLN])
VALUES
    ('SQL-Tutorial'),
    ('Oracle-Tutorial'),
    ('IBM DB2-Tutorial'),
    ('Sybase ASE-Tutorial'),
    ('PostgreSQL-Tutorial'),
    ('MariaDB-Tutorial'),
    ('MySQL-Tutorial'),
    ('Teradata-Tutorial'),
    ('IBM Informix-Tutorial'),
    ('Amazon SimpleDB-Tutorial')
;
SELECT  PARSENAME(REPLACE(coln,'-','.'),2) [before],
 PARSENAME(REPLACE(coln,'-','.'),1) [after]  from #Table1

output

before           after
SQL              Tutorial
Oracle           Tutorial
IBM DB2          Tutorial
Sybase ASE       Tutorial
PostgreSQL       Tutorial
MariaDB          Tutorial
MySQL            Tutorial
Teradata         Tutorial
IBM Informix     Tutorial
Amazon SimpleDB  Tutorial

Upvotes: 2

Dev
Dev

Reputation: 367

You Can use SUBSTRING and CHARINDEX to get before and after of any character like -

SELECT SUBSTRING(@String,0,CHARINDEX('-',@String,0)) As Before_str,
  SUBSTRING(@String,CHARINDEX('-',@String)+1,LEN(@String)) As After_str

Upvotes: 1

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

If you are using SQL Server the below code can extract everything before and after the char '-', the where condition is there to validate if there is a char '-' in first place to split like that

SELECT 
LEFT([COLUMN],charindex('-',[COLUMN])-1) as BeforeString,
RIGHT([COLUMN],len([COLUMN])-charindex('-',[COLUMN])) as AfterString
FROM [TABLE]
WHERE charindex('-',[COLUMN])<>0

Upvotes: 1

Related Questions