Simplify Row Values in SQL Output

Question

I have SQL code (abridged)

SELECT replace(replace(replace(replace(dbo.IDENTITY_MAP.Name,'My Company\',''),'-VLAN2',''),'.VLAN2\',''),'.Instr\','') as Site,
                   Count (CASE
                            WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                                AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1
            ............

            Group by replace(replace(replace(replace(dbo.IDENTITY_MAP.Name,'My Company\',''),'-VLAN2',''),'.VLAN2\',''),'.Instr\','')
            Order by Site

My output looks like this

enter image description here

But I wish for it to look like this, where all row values corresponding to Site '7' are summed, etc

enter image description here

I believe Regex would help, because this

^\w+(?:\s+\w+)?\\|[A-Z].*$

would isolate the number after the '\'

But when I attempt to do a replace statement with regex

SELECT replace(replace(replace(replace(replace(dbo.IDENTITY_MAP.Name,'My Company\',''),'-VLAN2',''),'.VLAN2\',''),'.Instr\',''), '^\w+(?:\s+\w+)?\\|[A-Z].*$') as Site,

I get error

The replace function requires 3 argument(s).

Please guide

Another Update

I tried this MS SQL code (abridged)

SELECT patindex('^\w+(?:\s+\w+)?\\|[A-Z].*$',replace(replace(replace(replace(dbo.IDENTITY_MAP.Name,'My Company\',''),'-VLAN2',''),'.VLAN2\',''),'.Instr\','')) as Site,
                   Count (CASE
                            WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                                AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1
                          END) AS 'Windows-SEP-11',

......
            Group by patindex('^\w+(?:\s+\w+)?\\|[A-Z].*$',replace(replace(replace(replace(dbo.IDENTITY_MAP.Name,'My Company\',''),'-VLAN2',''),'.VLAN2\',''),'.Instr\',''))
            Order by Site

And this is output

enter image description here

score 3 · Accepted Answer · edited May 23 '17 at 12:22

3

Try PATINDEX function, as suggested here: SQL Server Regular expressions in T-SQL

edited May 23 '17 at 12:22

Community

1
1

answered Jun 29 '15 at 14:54

RobW

128
2
10

It returns all 0s ...... I'll check my pattern, which I am using, i.e. `^\w+(?:\s+\w+)?\\|[A-Z].*$` – Rhonda Jun 29 '15 at 14:59
Yes, total output is 1249 rows, but desired result is 32 rows – Rhonda Jun 29 '15 at 15:04
That's not how you would use PATINDEX, SQL Server does not understand Regex expressions. You need to use PATINDEX and/or CHARINDEX to find what you are looking for in the string using SQL Server pattern searching method similar to the one you would use in LIKE (see this: https://msdn.microsoft.com/en-us/library/ms179859.aspx). – RobW Jun 29 '15 at 15:29
1

As a simpler solution, if you know the string is always going to conform to a specific rule (e.g. 2 characters after backslash always being the site number) you can use just CHARINDEX and SUBSTRING to get the site number as follows: SELECT CAST(SUBSTRING('Bronx\07X001',CHARINDEX('\',@String,1)+1, 2) – RobW Jun 29 '15 at 15:33
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/81889/discussion-between-sohni-mahiwal-and-robw). – Rhonda Jun 29 '15 at 16:37

score 0 · Answer 2 · answered Jun 29 '15 at 14:54

Without going to the hassle of writing a CLR library to be injected into SQL server to add Regex functionality (which you can do, it's just a pain in the ass: http://www.codeproject.com/Articles/42764/Regular-Expressions-in-MS-SQL-Server), you can't really. SQL server doesn't support Regex as-is.

The fundamental problem you have is that you're duplicating data in the "Site" column. Ideally you'd have a separate table for 'Sites' containing the name, identifier and the shorter number that you're interested in and add it as a foreign key to the table you're querying.

If for whatever reason you can't touch the structure of the database then I'd treat it as a presentation issue and do the grouping after the query.

My goodness, that will take quite a while. Even though the end product is only 32 rows ...... And I cannot touch structure of DB — Rhonda, Jun 29 '15 at 14:56

Simplify Row Values in SQL Output

Another Update

2 Answers2