Remove character between Pipe and semicolon

Question

I have an SQL Table with a column that stores keywords like this

keyword|44500903-8f09-40d8-a908-5fb3e03d145e;keyword2|441fb756-ff0a-473f-ad70-2f78d679e7d9

I need remove all characters between Pipe (including this) and semicolon, including this. The result must be:

Keyword; keyword2

Any ideas? I try to use, substring, trim etc... but didn't work.

SQL Server isn't really your friend here, if I am honest, however, what version of SQL Server? — Thom A, Feb 27 '23 at 17:54
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. — Yitzhak Khabinsky, Feb 27 '23 at 18:24
Would it be possible to normalize the database so that it did not have the problem of multiple values in one column? — Andrew Morton, Feb 27 '23 at 18:48

Thom A · Answer 1 · 2023-02-27T18:06:48.827

Assuming you are on SQL Server 2022, then you can use STRING_SPLIT to split the values into rows on the semicolon (;) with an ordinal position, then use LEFT and CHARINDEX to get the value up to the pipe (|), and finally reaggregate with STRING_AGG:

DECLARE @YourString varchar(500) = 'keyword|44500903-8f09-40d8-a908-5fb3e03d145e;keyword2|441fb756-ff0a-473f-ad70-2f78d679e7d9';

SELECT STRING_AGG(LEFT(SS.Value,CHARINDEX('|',SS.Value + '|')-1),'; ') WITHIN GROUP (ORDER BY SS.ordinal)
FROM STRING_SPLIT(@YourString,';',1) SS;

If you aren't on SQL Server 2022 you could use a different solution to split your values, such as a JSON splitter (2016+) or delimitedsplit8k_LEAD (2012+). If you aren't on SQL Server 2017+ you'll also need to switch out STRING_AGG for the "old" FOR XML PATH (and STUFF) solution.

JOOI, at what point does a CLR function start to look tempting? Would you normalise the database rather than do that? — Andrew Morton, Feb 27 '23 at 18:09
Oh, normalisation is ***always*** the correct answer, @AndrewMorton . — Thom A, Feb 27 '23 at 18:11

Yitzhak Khabinsky · Answer 2 · 2023-02-27T19:07:30.097

Here is a method that will work starting from SQL Server 2012 onwards.

We are tokenizing input string of tokens as XML. An intermediate result in t1(c) contains XML as follows:

<root>
  <r>keyword</r>
  <r>44500903-8f09-40d8-a908-5fb3e03d145e</r>
  <r>keyword2</r>
  <r>441fb756-ff0a-473f-ad70-2f78d679e7d9</r>
</root>

We just need to retrieve XML elements values in odd positions by using XPath predicate [position() mod 2 = 1].

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, tokens NVARCHAR(MAX));
INSERT @tbl (tokens) VALUES
(N'keyword|44500903-8f09-40d8-a908-5fb3e03d145e;keyword2|441fb756-ff0a-473f-ad70-2f78d679e7d9');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ';'
    , @pipe CHAR(1) = '|';

SELECT t.*
    , REPLACE(c.query('data(/root/r[position() mod  2 = 1])')
        .value('text()[1]','NVARCHAR(MAX)'), SPACE(1), @separator) AS result
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
    REPLACE(REPLACE(tokens,@pipe,@separator), @separator, ']]></r><r><![CDATA[') + 
    ']]></r></root>' AS XML)) AS t1(c);

Output

id	result
1	keyword;keyword2

Remove character between Pipe and semicolon

2 Answers2