CODEWITHSUNDEEP
sqlsql-serverparsing
Pr0no
Pr0no

Reputation: 4099

Parsing Comments and saving them in temp table

Via the frontend of a certain software package, administrators can add tags to each users in the system in a free textfield (called Comments). 

[teamlead:tom] [diet:vegan] [location:munich]

I know it would be much better if we could simply add columns to the Users table to add metadata to users, but we do not have this possibility.

I now need to query this free textfield, and basically query:

select userid, username, diet from Users where diet is not null;

For this, I have to parse the text in Comments, but I am unsure what the best approach is.

Can I do the parsing live in the query (table only has a few hundred users so performance should not be an issue)?

Does something like this require a 2-step process? For instance, first building a temp table with all values? If so, how could I parse the comments and build a temp table for all tags for each user?

Upvotes: 0

Views: 50

Answers (2)

Richard Boyce
Richard Boyce

Reputation: 413

Give this a go.

IF OBJECT_ID('tempdb..#Output') IS NOT NULL
    DROP TABLE #Output

CREATE TABLE #Output
    (UserID INT IDENTITY(1, 1),
     username NVARCHAR(MAX),
     diet NVARCHAR(MAX))

INSERT INTO #Output
SELECT REPLACE(REPLACE(SUBSTRING(Comments, CHARINDEX('[teamlead:', Comments, 1), CHARINDEX(']', Comments)), '[teamlead:', ''), ']', ''),
       REPLACE(REPLACE(SUBSTRING(Comments, CHARINDEX('[diet:', Comments, 1), CHARINDEX(']', Comments, 20) - LEN(SUBSTRING(Comments, CHARINDEX('[teamlead:', Comments, 1), CHARINDEX(']', Comments)))), '[diet:', ''), ']', '')
FROM Users

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

First, for your example query, you don't actually have to parse the comments. You can use like to look for keywords:

where comments like '%![diet:%' escape '!'

For a vegan diet:

where comments like '%![diet:vegan!]%' escape '!'

Of course, actually parsing the comments is desirable. The simplest way is to look for a split() function on the web (Google: "SQL Server split"). You can then use that to split the values. You might also find a way to convert these strings to XML, which would be an alternative approach.

Upvotes: 1

Related Questions