Return string between parentheses

Question

Post reply

Return string between parentheses

2Tall

SSChampion

Points: 14644
More actions
July 31, 2024 at 2:35 pm
Go to Answer
#4441449

Hi. I have a column that lists group names. Varying lengths. I want to extract the text between the parentheses within the string.
Example group names
Domain Administrators (abc.domain.com) Group Members - I would like to return abc.domain.com
Engineering Supervisors (xyz.domain.com) Members - I would like to return xyz.domain.com
The grey matter is not able to compute
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

ScottPletcher

SSC Guru

Points: 101192

July 31, 2024 at 5:05 pm

Answer

#4441492

SELECT
UserGroup.[Name]As GroupName,
UserGroup.[Description]As GroupDescription,
Domain
FROM [dbo].[UserGroup]
CROSS APPLY (
SELECT NULLIF(CHARINDEX('(', Name), 0) AS start_of_substring, 
    ISNULL(NULLIF(CHARINDEX(')', Name), 0), LEN(Name) + 1) AS end_of_substring
) AS ca1
CROSS APPLY (
SELECT LTRIM(RTRIM(SUBSTRING(Name, start_of_substring + 1, 
    end_of_substring - start_of_substring - 1))) AS Domain
) AS ca2
WHERE UserGroup.[Name] LIKE 'Domain Administrators%' or 
    UserGroup.[Name] LIKE 'Engineering Supervisors%'
ORDER BY UserGroup.[Name] ASC

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101192 More actions · Answer 1

DROP TABLE IF EXISTS #data;
CREATE TABLE #data ( string varchar(8000) NULL );
INSERT INTO #data VALUES( 'Domain Administrators (abc.domain.com) Group Members'), 
    ('Engineering Supervisors (xyz.domain.com) Members'), 
    ('Engineering Supervisor (xyz.domain.com'), 
    ('nothing to find here' )

SELECT string, result
FROM #data
CROSS APPLY (
SELECT NULLIF(CHARINDEX('(', string), 0) AS start_of_substring, 
    ISNULL(NULLIF(CHARINDEX(')', string), 0), LEN(string) + 1) AS end_of_substring
) AS ca1
CROSS APPLY (
SELECT LTRIM(RTRIM(SUBSTRING(string, start_of_substring + 1, 
    end_of_substring - start_of_substring - 1))) AS result
) AS ca2

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Chrissy321 SSCoach Points: 15789 More actions · Answer 2

This?

DECLARE @String VARCHAR(MAX) = '(abc.domain.com)'

SELECT @String,SUBSTRING(@String, CHARINDEX('(', @String) + 1, CHARINDEX(')', @String) - CHARINDEX('(', @String) - 1)

2Tall SSChampion Points: 14644 More actions · Answer 3

Hi. I am unclear how to update my query to use CROSS APPLY. My query below now returns the value with the ()

(abc.mydomain.com)

SELECT
UserGroup.[Name]As GroupName,
UserGroup.[Description]As GroupDescription,
SUBSTRING(Name,CHARINDEX('(',Name) +0,CHARINDEX(')',Name) - CHARINDEX('(',Name) +1) AS Domain
FROM [dbo].[UserGroup]
WHERE UserGroup.[Name] LIKE 'Domain Administrators%' or UserGroup.[Name] LIKE 'Engineering Supervisors%'
ORDER BY UserGroup.[Name] ASC

__PRESENT

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

2Tall SSChampion Points: 14644 More actions · Answer 4

I did try:

SUBSTRING(Name, CHARINDEX('(', Name) +1, CHARINDEX(')', Name) - CHARINDEX('(', Name) -1)

This returned:

Msg 537, Level 16, State 3, Line 1

Invalid length parameter passed to the LEFT or SUBSTRING function.

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Steve Collins SSCrazy Points: 2215 More actions · Answer 5

Maybe split twice using an ordinal string splitter. If there's more than 1 (www.domin.org)'s this would split them all. Splitter is available at this link

DROP TABLE IF EXISTS #data;
CREATE TABLE #data ( string varchar(8000) NULL );
INSERT INTO #data VALUES( 'Domain Administrators (abc.domain.com) Group Members'), 
    ('Engineering Supervisors (xyz.domain.com) Members (xyz.domain2.com) Two'), 
    ('Engineering Supervisor (xyz.domain.com'), 
    ('nothing to find here' )

/* using Jeff Moden ordinal splitter */
select *
from #data d
     cross apply dbo.DelimitedSplitN4K(d.string, '(') op
     cross apply dbo.DelimitedSplitN4K(op.Item, ')') cp
where op.ItemNumber>1 and cp.ItemNumber=1;

/* SQL Server 2022+ string split with optional ordinal parameter */
select *
from #data d
     cross apply string_split(d.string, '(', 1) op
     cross apply string_split(op.value, ')', 1) cp
where op.ordinal>1 and cp.ordinal=1;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

2Tall SSChampion Points: 14644 More actions · Answer 6

Thank you all for responding and taking time to help.

Many thanks,

Phil.

__PRESENT

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Return string between parentheses

Cookies on SQLServerCentral