SELECT FROM Ticket Table WHERE TicketTags

Hello,

I have a question about SQL statement. Maybe @QMcKay :smile:

I try to do a select in the ticket table to get the value of a ticket Tag I created so I can make a WHERE clause with that value

SELECT
   tkt.[TicketNumber]
  ,tkt.[Date]
  ,tkt.[TotalAmount]
  ,tkt.[TicketTags]
  ,tkt.[TransactionDocument_Id]
FROM [dbo].[Tickets] tkt  
WHERE value = ...

TicketTags will return something like this:

[{"TN":"SunatNumber","TT":0,"TV":"384"}]

And I would like to extract - inside the SQL - the value 384.

Is it possible ?

Marc


Using:

...
,Substring(tkt.[TicketTags],35,len(tkt.[TicketTags])-37) as SunatNumber
...

helps … for TicketTags =

[{"TN":"SunatNumber","TT":0,"TV":"274"}]
or
[{"TN":"SunatNumber","TT":0,"TV":"1041"}]

but sometimes I have 2 tickets TAGs =

[{"TN":"PorConsumo","TT":1,"TV":"1"},{"TN":"SunatNumber","TT":0,"TV":"783"}]

… in which case, it does not work.

ok, I found a way however it is kind of dirty …

Here are the different possible values I can have in Tickets tags:
[{“TN”:“SunatNumber”,“TT”:0,“TV”:“123”}]
[{“TN”:“SunatNumber”,“TT”:0,“TV”:“1234”}]
[{“TN”:“PorConsumo”,“TT”:1,“TV”:“1”},{“TN”:“SunatNumber”,“TT”:0,“TV”:“456”}]
[{“TN”:“PorConsumo”,“TT”:1,“TV”:“1”},{“TN”:“SunatNumber”,“TT”:0,“TV”:“4567”}]

Here is the sql I use to have the numbers only:

SELECT
   tkt.[TicketNumber]
  ,tkt.[Date]
  ,tkt.[TotalAmount]
  ,tkt.[TransactionDocument_Id]
  ,tkt.[Note]
  ,tkt.[TicketTags]
  ,Substring(replace(tkt.[TicketTags],'[{"TN":"PorConsumo","TT":1,"TV":"1"},',''),35,len(replace(tkt.[TicketTags],'[{"TN":"PorConsumo","TT":1,"TV":"1"},',''))-37) as SunatNumber
  ,len(tkt.[TicketTags]) as SunatlenNumber

FROM [SambaPOS4].[dbo].[Tickets] tkt

Using:

Substring(replace(tkt.[TicketTags],'[{"TN":"PorConsumo","TT":1,"TV":"1"},',''),35,len(replace(tkt.[TicketTags],'[{"TN":"PorConsumo","TT":1,"TV":"1"},',''))-37) as SunatNumber

will do it … until I had a third ticket tag one day if needed.