How do I extract all data in between two characters of a string in SQL ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

Hi Guys,

Please help me out to extract all the data between two particular characters in a column.

For example, I have data in a column data like,

"aaaaa( bb (asdads) bbb )"

And i need the output like,
"aaaaa"

Hint: Need to remove all the data in between "(" & ")" characters

Can anyone help me to get this produce this output in SQL.

What I have tried:

This query is not working properly as i expect,

SQL

SELECT SUBSTRING('aaaaa$bbb$bb$ccccc$ddddd',
CHARINDEX('$','aaaaa$bbbbb$ccccc')+1, 
CHARINDEX('$','aaaaa$bbbbb$ccccc',CHARINDEX('$','aaaaa$bbbbb$ccccc')+1) -
CHARINDEX('$','aaaaa$bbbbb$ccccc')-1) as My_String

Posted 18-Jul-17 11:05am

Raj 0203

Updated 28-Apr-19 5:05am

Add a Solution

Comments

PIEBALDconsult 18-Jul-17 19:56pm

SQL is not a good choice for string operations.
I prefer to use a CLR function that uses a Regular Expression.

Graeme_Grant 18-Jul-17 23:02pm

Google search is your friend: How-do-I-extract-all-data-in-between-two-character[^]

2 solutions

Solution 2

Suppose you have name column in table abc and values are-
"aaaaa( bb (asdads) bbb )".

Now I want to split only aaaaa from that table. 
So the query is 

select name,SUBSTRING_INDEX(name,'(',1) AS part1 from abc;

Posted 20-Jul-17 21:48pm

Eshika Roy

Comments

Member 13913124 16-Jul-18 6:24am

n the above solution 2 i want asdads as output, Can anyone suggest that query for me.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Atlapure Ambrish · Accepted Answer · 2017-07-18T17:42:00

Solution 1

Below is the query..

DECLARE @String NVARCHAR(max) ='aaaaa( bb (asdads) bbb )'

SELECT LEFT(@String, CHARINDEX('(', @String)-1) +'' +
RIGHT(@String, CHARINDEX(')', REVERSE(@String))-1)

Posted 18-Jul-17 17:42pm

Atlapure Ambrish

Updated 18-Jul-17 23:36pm

v2

Comments

Raj 0203 19-Jul-17 8:32am

This was awesome Ambrish...U saved my Life... Thank You SOOO much

Atlapure Ambrish 19-Jul-17 22:42pm

Its my pleasure!