SQL Server Remove multiple character strings within one string

Question

I have the following string of text in my database:

Value1 - Value2:  Value3 - Value4: Value5 - Value6:

I need to remove the dash AND everything between the dash up until the colon

The above result would become:

Value1: Value3: Value5:

Basicly, there could be endless amounts of values, but there could only be just a series of one.

Thing to note: The values could be any string!

Is there an easy way to do this? Preferably without a UDF. Could anyone help me out with this? Thanks in advance!

Edit: I agree this is a very poor implementation. The rest of the database itself isnt like this at all. It's just one table. The query I get from this will be used in a view where all values are seperated into multiple aliases. Thanks for understanding

Before you proceed it looks like very poor implementation of `EAV` using text in single column. I strongly recommend to stop and normalize your schema. Or at least use some sort of structured data like `XML`/`JSON` — Lukasz Szozda, Aug 17 '17 at 14:15
You should fix the data model to have one row per entity with each value in a separate row. If you cannot do this, you should explain why you are storing multiple values in a single string -- which is not the SQLish way to store data. — Gordon Linoff, Aug 17 '17 at 14:16
Please consider refactoring your database. This is a terrible design. Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** — Zohar Peled, Aug 17 '17 at 14:16
Is the delimiter always a colon? You are going to need a UDF for this because you have to split this into rows first and then shove it all back into a single column. Or use a recursive cte which would be even worse. — Sean Lange, Aug 17 '17 at 14:18

S3S · Answer 1 · 2017-08-17T14:27:02.773

You can use a split function... though your values shouldn't be stored like this in the first place.

declare @table table (col1 varchar(256))
insert into @table
values
('Value1 - Value2:  Value3 - Value4: Value5 - Value6:')

select
    ReturnVal = replace(ltrim(left(Item,charindex('-',Item))),'-',':')
from
    @table
    cross apply dbo.DelimitedSplit8K(col1,':')
where
    Item <> ''

RETURNS

+-----------+
| ReturnVal |
+-----------+
| Value1 :  |
| Value3 :  |
| Value5 :  |
+-----------+

Or, an ugly hack to get it back how you want it

select distinct
    --ReturnVal = replace(ltrim(left(Item,charindex('-',Item))),'-',':')
    ReturnVal = 'V' + STUFF((
          SELECT replace(left(Item,charindex('-',Item)),'-',':')
            FROM
            @table
            cross apply dbo.DelimitedSplit8K(col1,':')
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
from
    @table
    cross apply dbo.DelimitedSplit8K(col1,':')
where
    Item <> ''

RETURNS

ReturnVal
Value1 :  Value3 : Value5 :

JEFF MODEN SPLITTER

CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!

RETURNS TABLE WITH SCHEMABINDING AS
RETURN

/* "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
enough to cover VARCHAR(8000)*/

  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
GO

score 0 · Answer 2 · answered Aug 17 '17 at 14:18

SELECT substring(NameValue, 1, charindex('_', NameValue)-1) AS Names, 
  substring(NameValue, charindex('_', NameValue)+1, LEN(NameValue)) AS Values
FROM Table

EDIT: Something like this put in a function or stored procedure combined with a temp table should work for more than one line, depending on the line delimiter you should also remove CHAR(13) before you start:

DECLARE @helper varchar(512)
DECLARE @current varchar(512)
SET @helper = NAMEVALUE
WHILE CHARINDEX(CHAR(10), @helper) > 0 BEGIN
    SET @current = SUBSTRING(@helper, 1, CHARINDEX(CHAR(10), NAMEVALUE)-1)
    SELECT SUBSTRING(@current, 1, CHARINDEX('_', @current)-1) AS Names, 
      SUBSTRING(@current, CHARINDEX('_', @current)+1, LEN(@current)) AS Names
    SET @helper = SUBSTRING(@helper, CHARINDEX(CHAR(10), @helper)+1, LEN(@helper))
END
SELECT SUBSTRING(@helper, 1, CHARINDEX('_', @helper)-1) AS Names, 
  SUBSTRING(@helper, CHARINDEX('_', @helper)+1, LEN(@helper)) AS Names

Please don't use and suggest loops for splitting strings. The performance of this type of splitter is horrific. Here is a better option. http://www.sqlservercentral.com/articles/Tally+Table/72993/ And some other options. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Aug 17 '17 at 14:19

SQL Server Remove multiple character strings within one string

2 Answers2