How to find only last part of string with brackets using MS SQL

Question

I have a long string with multiple bracket inside string , i am tried to fetch only last part of the string , But i have achieved first part till end.

 Alkyl acrylate-Styrene (or alpha-Methylstyrene) copolymer (Substances not 
 requiring notification is limited to the following: Copolymer of butyl 
 acrylate / styrene (It is limited that the polymer is insoluble in water, acid 
 and alkali, and the content of the components having molecular weight less 
 than 1,000 is 1% or less.))

My Query :

SELECT  [RigNumSrc],[SubSRC],
case when  CHARINDEX(' (',[SubSRC])<>0  then LEFT ([SubSRC], charindex (' (', 
[SubSRC])-1)  
         when  CHARINDEX(' (',[SubSRC])=0 then [SubSRC] end as [chemnamesrc1]
 ,case when  CHARINDEX(' (',[SubSRC])<>0  then SUBSTRING([SubSRC], 
CHARINDEX(' 
(',[SubSRC]),LEN([SubSRC]))
end as synamesrc1 from xyztable

my result :

    (or alpha-Methylstyrene) copolymer (Substances not requiring notification 
    is limited to the following: Copolymer of butyl acrylate / styrene (It is 
    limited that the polymer is insoluble in water, acid and alkali, and the 
    content of the components having molecular weight less than 1,000 is 1% 
     or less.))

Please help me to find only :

 (It is limited that the polymer is insoluble in water, acid 
 and alkali, and the content of the components having molecular weight less 
 than 1,000 is 1% or less.)

I strongly recommend you use something else than SQL Server. String manipulation is not it's forté. Especially considering you want the value of the text that is within parenthesis within parenthesis; this starts to almost create a hierarchical relationship. — Thom A, Jul 19 '18 at 09:53

Killer Queen · Accepted Answer · 2018-07-19T12:25:21.963

2

TRY :

DECLARE @myString VARCHAR(MAX)='Alkyl acrylate-Styrene (or alpha-Methylstyrene) copolymer (Substances not requiring notification is limited to the following: Copolymer of butyl 
acrylate ( styrene (It is limited that the polymer is insoluble in water, acid and alkali, and the content of the components having molecular weight less than 1,000 is 1% or less.))'

SELECT CASE WHEN RIGHT(@myString,1) <> ')' THEN NULL ELSE REPLACE(RIGHT(@myString , CHARINDEX ('(' ,REVERSE(@myString))),'))',')') END

edited Jul 19 '18 at 12:25

answered Jul 19 '18 at 09:59

Killer Queen

776
9
20

Starting `(` is missing in output I think! – Prashant Pimpale Jul 19 '18 at 10:00
@Larnu What final text? it is about `'))'`? If this is not a bad thing, it is only to replace `'))'` if it exists. – Killer Queen Jul 19 '18 at 10:04
Playing Devil's advocate here, but try the string: `DECLARE @myString VARCHAR(MAX)='Alkyl acrylate-Styrene (or alpha-Methylstyrene) copolymer (Substances not requiring notification is limited to the following: Copolymer of butyl acrylate: (styrene (It is limited that the polymer is insoluble in water (acid and alkali) and the content of the components having molecular weight less than 1,000 is 1% or less.))'` – Thom A Jul 19 '18 at 10:05
@Larnu this case is not included, the question is whether such a case occurs? owner of the post? – Killer Queen Jul 19 '18 at 10:08
@KillerQueen i agree above query will work , what if something ends only with ')' other than "))". Do we need to write case statement. – Jul 19 '18 at 10:30
@KillerQueen thank you . I am trying to get only single bracket at the end. – Jul 19 '18 at 10:49
the query will work if we are looking for the last `'('` @DataBase – Killer Queen Jul 19 '18 at 10:52
ya it will work and will take last string with string and end with )) . i am trying to get ). @KillerQueen – Jul 19 '18 at 10:55
it is working fine . but it is clearing even string :> for Residual oil(kerosene)after extraction of normal paraffin . The output is (kerosene)after extraction of normal paraffin – Jul 19 '18 at 11:11
For `Residual oil(kerosene)after extraction of normal paraffin` what should be the output ? @DataBase – Killer Queen Jul 19 '18 at 11:18
@KillerQueen . output should be null . only when there is bracket at the last in the string it should give output. or null – Jul 19 '18 at 12:15
thank you @killer i have tried using case statement and it worked . could you please help me to find remaining word left out other than last bracket – Jul 19 '18 at 12:28
Sure, I do not know if I understand, give an example of input and output @DataBase – Killer Queen Jul 19 '18 at 12:32
output 1: Alkyl acrylate-Styrene (or alpha-Methylstyrene) copolymer (Substances not requiring notification is limited to the following: Copolymer of butyl acrylate / styrene output 2: (It is limited that the polymer is insoluble in water, acid and alkali, and the content of the components having molecular weight less than 1,000 is 1% or less.) @KillerQueen – Jul 19 '18 at 12:36
`SELECT LEFT(@myString, LEN(@myString) - CHARINDEX('(',REVERSE(@myString))), CASE WHEN RIGHT(@myString,1) <> ')' THEN NULL ELSE REPLACE(RIGHT(@myString , CHARINDEX ('(' ,REVERSE(@myString))),'))',')') END ` @DataBase – Killer Queen Jul 19 '18 at 12:39
Unless you only need this result when the last char is `')'` then SELECT `CASE WHEN RIGHT(@myString,1) <> ')' THEN NULL ELSE LEFT(@myString, LEN(@myString) - CHARINDEX('(',REVERSE(@myString))) END, CASE WHEN RIGHT(@myString,1) <> ')' THEN NULL ELSE REPLACE(RIGHT(@myString , CHARINDEX ('(' ,REVERSE(@myString))),'))',')') END` – Killer Queen Jul 19 '18 at 12:42
@KillerQueen . it is clearing even for Mixture of N,N'-bis(or N,N-bis or N,N,N'-tris or N,N,N',N'-tetrakis[3-(trimethoxysilyl)propyl]ethylenediamine i think we need to put case for this also/ – Jul 19 '18 at 12:46
You need help? @DataBase – Killer Queen Jul 19 '18 at 12:49
@KillerQueen your genius . How can i thank my best :) – Jul 19 '18 at 12:51
I'm glad that I could help, have nice day :) @DataBase – Killer Queen Jul 19 '18 at 12:58
please suggest me . how could be master in sql server. Any tip from your end :) – Jul 19 '18 at 13:19
The only thing that comes to my mind is practice:) @DataBase – Killer Queen Jul 19 '18 at 13:22
@KillerQueen could ypu please help me on this . https://stackoverflow.com/questions/51479456/why-data-is-truncated-when-importing-from-xml-to-an-access-database – Jul 23 '18 at 14:12
Sorry, i was on holiday @DataBase – Killer Queen Jul 30 '18 at 05:16
@KillerQueen .Ya its ok :) – Aug 02 '18 at 11:05

How to find only last part of string with brackets using MS SQL

1 Answers1