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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy