CODEWITHSUNDEEP
sqlsql-servert-sql
Derek A
Derek A

Reputation: 65

How to query a single SQL column and get only a specific part of the string

I am trying to query and show all the unique BillingCodes in a column, as well as get the total counts of how many times each BillingCode shows up.

The issue is, the BillingCode is not separated into its own Column, but is part of a string within the column that may be longer or shorter depending on the account's settings.

The thing that does stay the same and that may help here, is that the billing code itself is contained within tags 12345

I am trying to return ONLY what is contained between these tags within the RawXml column

.

I have this set up on my own test SQL server to try to figure this out, but I have not had luck so far with figuring this out.

Here is a SQL Query to create a new table with examples of what I am working with

CREATE TABLE mytable
    (Column1 varchar(100))
;

INSERT INTO mytable
    (Column1)
VALUES
('<Xml><Description /><BillingCode>Billing1</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing12</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>TEST</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>BillingCodeHere</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing3</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>123</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing2</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>12345</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing1</BillingCode></Xml>'),
('<Xml><Description>Line 1</Description><BillingCode>BillingCodeHere</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing1</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing2</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing1</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>SomethingHere</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing2</BillingCode></Xml>')

.

I think this is the closest that I have gotten to getting just the billing code. I feel that I am close, but I have been stuck here.

Select Right(RawXml, LEN(RawXml) - CHARINDEX('<BillingCode>', RawXml, 1) - 12)
From dbo.mytable

The result set would look like this

Billing1</BillingCode></Xml>
Billing12</BillingCode></Xml>
TEST</BillingCode></Xml>
BillingCodeHere</BillingCode></Xml>

.

I would like the output to be just what is contained between the billing code tags ???

From here, I would be able to get a total count of each unique billing code. 

BillingCode     Total
TEST            1
SomethingHere   1
BillingCodeHere 2
Billing1        4
Billing2        3
Billing3        1

etc...

Upvotes: 4

Views: 51

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176264

Using XML methods:

SELECT BillingCode, COUNT(*)
FROM  mytable
CROSS APPLY (SELECT CAST(Column1 AS XML)) u(v)
CROSS APPLY u.v.nodes('//BillingCode') s(c)
CROSS APPLY (SELECT s.c.value('.', 'nvarchar(128)')) AS z(BillingCode)
GROUP BY BillingCode;

db<>fiddle demo

Upvotes: 3

Related Questions