Regex Equivalent?

Question

Regex Equivalent?

lanky_doodle

SSCrazy

Points: 2378
More actions
November 23, 2020 at 9:20 pm

#3813199

Hi,
Taking some sample data:
Microsoft Windows NT Workstation 10.0
Microsoft Windows NT Workstation 10.0
Microsoft Windows NT Workstation 10.0
Microsoft Windows NT Server 10.0
Microsoft Windows NT Workstation 10.0
Microsoft Windows NT Workstation 10.0
Microsoft Windows NT Workstation 10.0
How can I just extract the word that is either Server or Workstation? I've posted in 2019 forum but need something supported in probably 2012 (and up).
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Use LIKE with leading and trailing wildcards with the understanding that it will always cause a table or index scan (i.e. Non-SARGable query criteria). If you want a coded answer, post coded data. Please see the first link in my signature line below for one way to do that.

EDIT: Like will only find the rows that contain the words. Reading Sergiy's interpretation of your post, I have to ask, what do you mean by "extract" the words?

This reply was modified 3 years, 5 months ago by Jeff Moden. Reason: Edited for clarification

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110206 More actions · Answer 2

Not sure I can understand the request.

can you indicate what do you expect as an outcome?

_____________
Code for TallyGenerator

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 3

DECLARE @Data TABLE (Name nvarchar(100));
INSERT INTO @Data(Name)
VALUES 
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Server 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Workstation 10.0')

SELECT Name,
       CASE WHEN Name LIKE '%Server%' THEN 'Server'
            WHEN Name LIKE '%Workstation%' THEN 'Workstation'
            ELSE 'None'
       END WindowType
FROM @Data d

Sergiy SSC Guru Points: 110206 More actions · Answer 4

Jonathan AC Roberts wrote:

DECLARE @Data TABLE (Name nvarchar(100));
INSERT INTO @Data(Name)
VALUES 
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Server 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Workstation 10.0')

SELECT Name,
       CASE WHEN Name LIKE '%Server%' THEN 'Server'
            WHEN Name LIKE '%Workstation%' THEN 'Workstation'
            ELSE 'None'
       END WindowType
FROM @Data d

That is a terrible way to do T-SQL.

Well, in any other language, I believe, hardcoding data is a pretty lousy practice.

This must be more like it:

DECLARE @Data TABLE (Name nvarchar(100));
INSERT INTO @Data(Name)
VALUES 
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Server 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Playstatuion 5')

DECLARE @WordsToSeek TABLE (Word nvarchar(50));
INSERT INTO @WordsToSeek (Word)
SELECT 'Server'
UNION ALL 
SELECT 'Workstation';

SELECT Name, ISNULL(w.Word, 'None') WindowsType
FROM @Data d
LEFT JOIN @WordsToSeek w ON d.Name LIKE '%' + w.Word + '%'

_____________
Code for TallyGenerator