CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Connie DeCinko
Connie DeCinko

Reputation: 191

Parse a string into various values

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>

Upvotes: 0

Views: 113

Answers (2)

void
void

Reputation: 7880

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 |

Upvotes: 2

M.Ali
M.Ali

Reputation: 69504

First Create a Split function. then use the following query .

Split Function

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

Test Data & Query

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

Result Set

╔═════════════════════╦═══════════════╦══════════╦═══════════╗
║      EventName      ║ AccountNumber ║ Quantity ║ PricePaid ║
╠═════════════════════╬═══════════════╬══════════╬═══════════╣
║ Education Luncheon  ║ 501-2620      ║       2  ║  $110     ║
║ Womens Breakfast    ║ 512-2620      ║       2  ║  $80      ║
║ Friday Luncheon     ║ 502-26200     ║       2  ║  110      ║
╚═════════════════════╩═══════════════╩══════════╩═══════════╝

Upvotes: 1

Related Questions