SQL Syntax Select Custom String from an Array not selecting Correctly

Question

The Issue:

I am trying to select from multiple tables, Table A and B but this is mainly regarding the Only column I am choosing from Table B. The values in this column contain an Array of string.

Aim is to get get rid of any [] and then look at string. There are two main types of strings, one that begins with “some text needed”, and others that have same "some text needed" followed by comma and further text, Other type of string contain random text NOT Starting with "some text needed".the examples include:

• "some text needed"
• "some text needed, other"
• "some text needed, extra text"
• "some text needed, extra - text, required"
• "today is a rainy day"
• "Extra hot"

What is Required:

For condition where string does not start with "some text needed" simply return the text value and for others return the values after the comma (,) while ignoring "some text needed," so the output would look something like:

Desired Results:

• "other"
• "extra text"
• "extra - text, required"
• "today is a rainy day"
• "Extra hot"

I have used following SQL code, which gets rid of [ and ] but it is always bringing me back "some text needed, other" instead of just "other"

SELECT
    col1, col2, ..., col10,
    CASE
        WHEN CHARINDEX('[', cier.if_text_is_required) > 0
        THEN REPLACE(REPLACE(cier.if_text_is_required, '[', ''), ']', '')
        WHEN CHARINDEX('some text needed,', cier.if_text_is_required) > 0
        THEN
            CASE
                WHEN CHARINDEX(',', cier.if_text_is_required, CHARINDEX('some text needed,', cier.if_text_is_required) + LEN('some text needed,')) > 0
                THEN LTRIM(SUBSTRING(cier.if_text_is_required, CHARINDEX(',', cier.if_text_is_required, CHARINDEX('some text needed,', cier.if_text_is_required) + LEN('some text needed,')) + 2, LEN(cier.if_text_is_required)))
                ELSE cier.if_text_is_required
            END
        ELSE cier.if_text_is_required
    END AS if_text_is_required

FROM TableA
LEFT JOIN (
    SELECT id, if_text_is_required, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC, rn_id DESC) AS rn
    FROM TableB
    WHERE if_text_is_required IS NOT NULL
) AS cier ON TableA.id = cier.id AND cier.rn = 1

I can get rid of [ and ] however, I am unable to SELECT Only the string after the comma when string contains, "some text needed".

Can someone spot any issues with the above SQL and can assist me? All assistance is appreciated.

Answer 1

SELECT 
      A.*,
      CASE 
          WHEN B.YourColumn LIKE 'some text needed%' 

          THEN STUFF(B.YourColumn, 1, CHARINDEX(',', B.YourColumn + ','), '')
          ELSE REPLACE(REPLACE(B.YourColumn, '[', ''), ']', '')
      END as manipulated_column
    FROM 
      TableA AS A
    JOIN 
      TableB AS B 
      ON A.JoinColumn = B.JoinColumn

• STUFF(): This function is used to remove a part of the string and replace it with another string.
• CHARINDEX(): This finds the index of the first occurrence of a string within another one.
• REPLACE(): This is used twice to remove both the '[' and ']'

Answer 2

Here's how I'd go about it. I've created my own dataset here since none was provided in the question, and got rid of the joins and expressions that are immaterial to the question. Ostensibly, your question is about formatting the strings. So I'll start with this data:

drop table if exists #data
select String
into #data
from
(
    values
    ('[some text needed]'),
    ('[some text needed, other]'),
    ('[some text needed, extra text]'),
    ('[some text needed, extra - text, required]'),
    ('[today is a rainy day]'),
    ('[Extra hot]')

) a (string)

Again, I think the square brackets question is probably immaterial to the question since you even said it's not causing you any problems. But I'll leave them in since you mentioned it.

The first thing I'd do is strip off the square brackets. You can do that with the trim function and providing a list of characters to trim. In this case, I've chosen to trim [] to get rid of the brackets and any whitespace that might remain.

First, I'm going to assign that special value "some text needed" to a variable so I don't have to re-type it everyone.

Then you just have to handle your different cases. Since you can have a string which is just "Some text needed" with no item after it, let's treat that as a special case, and set its output value to an empty string.

The other special case is when we have "some text needed" followed by a comma, and some other stuff. So we'll check to see if your cleaned-up string starts with "some text needed," (notice the comma). I like to handle that by using the stuff function. STUFF is great for when you need to get rid of some number of characters from the start of your string. So here I'm removing all the characters up to that comma, and adding 2 (one because the stuff function needs the index of the next character, and one more because if i just take len(@Slug) it won't account for the comma).

Then of course the default case is to just echo the original string.

declare @slug nvarchar(128) = 'some text needed'

select
    String,
    TrimmedString,
    case
        when TrimmedString = @slug then ''
        when TrimmedString like @Slug + ',%' then stuff(TrimmedString, 1, len(@Slug) + 2, '')
        else TrimmedString
    end
from #data
cross apply
(
   select TrimmedString = trim('[] ' from string)
) b

Note, if you're running a version of SQL Server that doesn't support TRIM, you can just replace that function call with the code you already had working. This is just a shortcut if you have access to it.

Answer 3

Changing Case When to ‘some text needed,%' (adding comma) helps deal with issue and returns everything without square brackets.

SELECT 
      A.*,
      CASE 
          WHEN B.YourColumn LIKE '

          THEN STUFF(B.YourColumn, 1, CHARINDEX(',', B.YourColumn + ','), '')
          ELSE REPLACE(REPLACE(B.YourColumn, '[', ''), ']', '')
      END as manipulated_column
    FROM 
      TableA AS A
    JOIN 
      TableB AS B 
      ON A.JoinColumn = B.JoinColum

However, I am unable to SELECT only the second part of script after the comma. If string has 2 parts separated by comma I only want to select everything after first comma, if multiple commas then Only string after comma until third comma (excluding third comma).

Hope it helps explain the issue