Assuming you have the string below, how would you recommend parsing it out into its various values? Each "record" ends with a break tag. The first value is the event name, the second between the parentheses is the account number, next is the ticket quantity and lastly is the total price paid.
Education Luncheon (501-2620) - 2 - $110<br>Womens Breakfast (512-2620) - 2 - $80<br>Friday Luncheon (502-26200) - 2 - 110<br>
I want to so say a direct query with use of XML parsing and PARSENAME and REPLACE functions without using any UDF:
Suppose this string:
DECLARE @str VARCHAR(MAX)='Education Luncheon (501-2620) - 2 - $110<br>Womens Breakfast (512-2620) - 2 - $80<br>Friday Luncheon (502-26200) - 2 - 110<br>'
now just with converting that string to XML and parsing it inside a cte and finally using the mentioned string functions:
;WITH cte AS(
SELECT Value = tbl.fld.value('(./text())[1]', 'nvarchar(4000)')
FROM
(
SELECT
v = CONVERT(XML, '<i>'
+ REPLACE(@str, '<br>' , '</i><i>')
+ '</i>')
) AS a
CROSS APPLY v.nodes('i') AS tbl(fld)
WHERE tbl.fld.value('(./text())[1]', 'nvarchar(4000)') IS NOT NULL
)
SELECT PARSENAME(REPLACE(Value ,'(','.'),2) AS [Event],
PARSENAME(REPLACE(REPLACE(Value ,'(','.') ,')','.'),2)AS AccNumber,
PARSENAME(REPLACE(Value ,'-','.'),2) AS Qty ,
PARSENAME(REPLACE(Value ,'-','.'),1) AS Price
FROM cte
We will get this Result:
| Event | AccNumber | Qty | Price |
|---------------------|-----------|-----|-------|
| Education Luncheon | 501-2620 | 2 | $110 |
| Womens Breakfast | 512-2620 | 2 | $80 |
| Friday Luncheon | 502-26200 | 2 | 110 |
First Create a Split function. then use the following query .
CREATE FUNCTION dbo.Split
(
@delimited nvarchar(max),
@delimiter nvarchar(100)
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
id int identity(1,1), -- I use this column for numbering splitted parts
val nvarchar(max)
)
AS
BEGIN
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
select
r.value('.','varchar(max)') as item
from @xml.nodes('//root/r') as records(r)
RETURN
END
GO
DECLARE @String VARCHAR(1000) = 'Education Luncheon (501-2620) - 2 - $110<br>Womens Breakfast (512-2620) - 2 - $80<br>Friday Luncheon (502-26200) - 2 - 110<br>'
SELECT LEFT(val , CHARINDEX('(', Val) -1 ) AS EventName
,REPLACE( REPLACE(
SUBSTRING(Val , CHARINDEX('(', Val) , LEN(Val) - CHARINDEX(')', Val))
,'(',''),')','') AS AccountNumber
,REPLACE(SUBSTRING(Val,
CHARINDEX(') -', Val) +2, LEN(Val) - CHARINDEX(') -', Val)
- CHARINDEX('-', REVERSE(Val)))
,'-','') AS Quantity
,PARSENAME(REPLACE(Val ,'-','.'),1) AS PricePaid
FROM dbo.Split(@String, '<br>')
WHERE NULLIF(val ,'') IS NOT NULL
╔═════════════════════╦═══════════════╦══════════╦═══════════╗
║ EventName ║ AccountNumber ║ Quantity ║ PricePaid ║
╠═════════════════════╬═══════════════╬══════════╬═══════════╣
║ Education Luncheon ║ 501-2620 ║ 2 ║ $110 ║
║ Womens Breakfast ║ 512-2620 ║ 2 ║ $80 ║
║ Friday Luncheon ║ 502-26200 ║ 2 ║ 110 ║
╚═════════════════════╩═══════════════╩══════════╩═══════════╝
