CODEWITHSUNDEEP
sqlsql-servert-sqltextdata-retrieval
erasmo carlos
erasmo carlos

Reputation: 682

SQL retrieve string that have special character

I am trying to write a query that select only the values that have a '+' at the end.

These values are pieces of text that users select from a screen that serves them a list of reference comments. The different special characters that a pieces of text can have at the end are: '+' or '~' or '|'

The values are stored in a single column, and are mixed.

For this task I only need to get the comments that end with a '+'.

Here is an example of the text that is stored:

    Addendum and/or contract providing additional event details and conditions.+

There are charges for these services.+

Notify Mall Crew of electrical needs for activities.+

Provide and maintain access to the Hotel1 during event.~

Provide and maintain access to the Hotel2 during event.~

Event organizer/sponsor is responsible for cleanup of event area1.|

Event organizer/sponsor is responsible for cleanup of event area2.|

Here is my query (not working) it returns nothing.

SELECT
    COMMENT
FROM 
    PERMIT A
INNER JOIN 
    PROCESS G ON A.CODE = G.CODE 
WHERE  
    RIGHT(COMMENT, 2) = '+'
    AND COMMENT <> ''

enter image description here

Thank you much in advance for your help.

Upvotes: 0

Views: 1903

Answers (1)

Hogan
Hogan

Reputation: 70513

So I did not test this, it may have typos, but the basic idea is to convert your text into XML and then use the XML tools to query the result. This is much easier than trying to parse a string with a dynamic number of lines.

First we extract the xml string from the table converting the whole thing to an xml string with rows for each line and use replace on the CR LF to make seperators:

SET @xmlstr  = 
  SELECT CAST('<file><row>' + REPLACE(COMMENT,CHAR(13)+CHAR(10),'</row><row>') + '</row></file>' AS XML)
  FROM PERMIT A
  INNER JOIN PROCESS G ON A.CODE = G.CODE

Then select the lines you want from the XML

SELECT line.a_row
FROM    @xmlstr.nodes('/file') AS line(a_row)
WHERE right(line.a_row,1) = '+'

You can find many examples of using this trick (converting to xml to aid in parsing) on this website and around the web.

Upvotes: 1

Related Questions