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
July 31, 2024 at 2:47 pm
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".
July 31, 2024 at 2:48 pm
This?
DECLARE @String VARCHAR(MAX) = '(abc.domain.com)'
SELECT @String,SUBSTRING(@String, CHARINDEX('(', @String) + 1, CHARINDEX(')', @String) - CHARINDEX('(', @String) - 1)
July 31, 2024 at 3:10 pm
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
July 31, 2024 at 3:13 pm
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.
<object id="__symantecMPKIClientMessenger" style="display: none;" data-supports-flavor-configuration="true" data-extension-version="1.2.0.158"></object>__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
July 31, 2024 at 4:16 pm
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
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".
August 1, 2024 at 5:56 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply