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?
-
so you need to the values as two columns?TechDo– TechDo2014-04-29 07:02:35 +00:00Commented Apr 29, 2014 at 7:02
-
1You have a poorly designed column then. If these are actually two separate pieces of data, they ought to be stored in their own, separate, columns. If so desired, you could then have a computed column that assembles them together and adds the brackets, for display purposes.Damien_The_Unbeliever– Damien_The_Unbeliever2014-04-29 07:03:43 +00:00Commented Apr 29, 2014 at 7:03
-
Yes , I need them as two columns.@TechDouser3383390– user33833902014-04-29 07:04:49 +00:00Commented Apr 29, 2014 at 7:04
-
Its not a poorly designed Column. Its just a client requirement that they wanted to separate certain part of a code. @Damien_The_Unbelieveruser3383390– user33833902014-04-29 07:05:40 +00:00Commented Apr 29, 2014 at 7:05
Add a comment
|
4 Answers
Please try:
declare @var nvarchar(100)='xxxxxxx(xxxxx)'
select @var,
LEFT(@var, charindex('(', @var, 1)-1),
replace(right(@var, charindex('(', @var, 1)-1), ')', '')
Comments
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)
Comments
` 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.
Comments
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
