CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user3383390
user3383390

Reputation: 211

Extracting a part of String in SQL Server

I have a field that returns a string value in this format xxxxxxx(xxxxx). Now I need to extract the content of the string inside () as well the ones before (). How can I achieve this?

Upvotes: 0

Views: 134

Answers (4)

knkarthick24
knkarthick24

Reputation: 3216

TEchDo answer is perfect, he just missed out one other replace function for the second column. 

declare @var nvarchar(100)='xxxxxxx(xxxxx)'  

select @var AS Source, LEFT(@var, charindex('(', @var, 1)-1) AS FirstColumn,   
replace(replace(right(@var, charindex('(', @var, 1)-1), ')',''),'(','') AS SecondColumn

Upvotes: 0

dpk
dpk

Reputation: 641

` Dont have much idea about sql server.

bt in oracle you can achieve this as follow:

SELECT SUBSTR('xxxxxxx(xxxxx)',0,instr('xxxxxxx(xxxxx)','(')-1)            AS first_part,
SUBSTR('xxxxxxx(xxxxx)',instr('xxxxxxx(xxxxx)','(')+1,instr('xxxxxxx(xxxxx)',')')-instr('xxxxxxx(xxxxx)','(')-1) AS second_part
FROM dual;

so try to find the respective syntax. substr is self explanatory. and instr returns the position of 1st occurrence of a character in a given string.

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

declare @str varchar(100)

set @str='xxxxx(aaa)'

---- extracts xxxxx
select SUBSTRING(@str,0,CHARINDEX('(',@str)) 

---- extracts aaa
select SUBSTRING(@str,CHARINDEX('(',@str)+1,CHARINDEX(')',@str)-CHARINDEX('(',@str)-1)

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please try:

declare @var nvarchar(100)='xxxxxxx(xxxxx)'

select @var, 
    LEFT(@var, charindex('(', @var, 1)-1), 
    replace(right(@var, charindex('(', @var, 1)-1), ')', '')

Upvotes: 1

Related Questions