I'm trying to replace anything between 2 specific characters in a string that contains multiples of those 2 caracters. Take it as a csv format.

Here an example of what i got as data in that field:

0001, ABCD1234;0002, EFGH432562;0003, IJKL1345hsth;...

What I need to retreive from it is all parts before the ',' but not what are between ',' and ';'

I tried with those formula but no success

 SELECT REPLACE(fieldname, ',[A-Z];', ' ') FROM ...
 or
 SELECT REPLACE(fieldname, ',*;', ' ') FROM ...

I need to get

0001 0002 0003

Is there a way to achieve that?

CodePudding user response：

You can CROSS APPLY to a STRING_SPLIT that uses STRING_AGG (since Sql Server 2017) to stick the numbers back together.

select id, codes
from your_table
cross apply (
  select string_agg(left(value, patindex('%_,%', value)), ' ') as codes
  from string_split(fieldname, ';') s
  where value like '%_,%'
) ca;
GO

Demo on db<>fiddle here

CodePudding user response：

I would use splitter function first and find the char ',' position to substring it.

My splitter function :

CREATE FUNCTION [dbo].[Split_Comma_Delimited] (
      @InputString                  VARCHAR(8000),
      @Delimiter                    VARCHAR(50)
)

RETURNS @Items TABLE (
      Item                          VARCHAR(8000)
)

AS
BEGIN
      IF @Delimiter = ' '
      BEGIN
            SET @Delimiter = ','
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
      END

      IF (@Delimiter IS NULL OR @Delimiter = '')
            SET @Delimiter = ','

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
--INSERT INTO @Items VALUES (@InputString) -- Diagnostic

      DECLARE @Item                 VARCHAR(8000)
      DECLARE @ItemList       VARCHAR(8000)
      DECLARE @DelimIndex     INT

      SET @ItemList = @InputString
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      WHILE (@DelimIndex != 0)
      BEGIN
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
            INSERT INTO @Items VALUES (@Item)

            -- Set @ItemList = @ItemList minus one less item
            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex 1, LEN(@ItemList)-@DelimIndex)
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      END -- End WHILE

      IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
      BEGIN
            SET @Item = @ItemList
            INSERT INTO @Items VALUES (@Item)
      END

      -- No delimiters were encountered in @InputString, so just return @InputString
      ELSE INSERT INTO @Items VALUES (@InputString)

      RETURN

END -- End Function
GO

Afterwards using this :

select  * , CHARINDEX(',', Item) , SUBSTRING(Item,0,CHARINDEX(',', Item)) column_you_want FROM 
[dbo].[Split_Comma_Delimited] ('0001, ABCD1234;0002, EFGH432562;0003, IJKL1345hsth;' , ';')