How to use a part of a function as a column of a table

tamiml · March 9, 2024, 2:59pm

hello there,
in a table b25 the column "name" includes strings
in a table b26 the column "char_omitted" includes characters that have to be omitted from the strings of table b25
the table b26 is dynamic and can be changed by user from time to time

see below table b25

NAME
LORENZO BROWN
LORENZOA BROWN
LORENZOC BROWN
LORENZOD BROWN
LORENZOF BROWN

see below table b26

CHAR_OMITTED
A
C
D
F

this is the expected result:

below is tne tables script

create table b25 (name nvarchar(50))
insert into b25 select 'LORENZO BROWN'
insert into b25 select 'LORENZOA BROWN'
insert into b25 select 'LORENZOC BROWN'
insert into b25 select 'LORENZOD BROWN'
insert into b25 select 'LORENZOF BROWN'

create table b26 (char_omitted nvarchar(50))
insert into b26 select 'A'
insert into b26 select 'C'
insert into b26 select 'D'
insert into b26 select 'F'

harishgg1 · March 10, 2024, 7:12am

hi

hope this helps

; with cte as 
( select string_agg(char_omitted,'') as ok  from b26 )
select 
    name
  , REPLACE(TRANSLATE(name, ok, '@@@@'), '@', '') as correct_name 
from 
   cte , b25

harishgg1 · March 10, 2024, 11:19am

hi

hope this helps

another way of doing this

shorter way

there can never be $ in the name

select 
     a.name 
   , replace(a.name,isnull(b.char_omitted,'$'),'') as correct_name 
from 
   b25 a left join b26 b 
        on a.name like '%'+b.char_omitted+'%'

gdl · March 10, 2024, 11:34am

To make the code more general, CORRECT_NAME could be calculated like this:

, replace(translate(name, ok, REPLICATE('@', LEN(OK))), '@', '') as correct_name

gdl · March 10, 2024, 11:36am

No, this second solution doesn't work in general.
Try adding the record to b26

insert into b26 select 'B'

harishgg1 · March 10, 2024, 11:42am

hi

my 2nd solution was based purely on the scenario given by @tamiml

all kinds of data scenarios are possible

thanks

tamiml · March 11, 2024, 10:24pm

Thank you for your reply however the tables should be updated without having to change the code. When I added a record to table b26 I had to change the function. Instead of @@@@, we have to write @@@@@

tamiml · March 11, 2024, 11:02pm

Thank you for your reply however the tables should be updated with out having to change the code. When I added a record to table b26 I got 9 records instead of 5.

harishgg1 · March 12, 2024, 6:22am

hi
use this given by @gdl

, replace(translate(name, ok, REPLICATE('@', LEN(OK))), '@', '') as correct_name

tamiml · March 12, 2024, 10:02am

thank you for your reply, however it seems to me that it works if the values in table b26 includes one character. in practise these values can include more than one character.

harishgg1 · March 12, 2024, 11:15am

hi

can you show
= main table data
= chars omitted ( multiple characters )
= out put

?????

thank you

tamiml · March 12, 2024, 2:30pm

create table b25 (name nvarchar(50))
insert into b25 select 'LORENZO BROWN'
insert into b25 select 'LORENZOA BROWN'
insert into b25 select 'LORENZOC BROWN'
insert into b25 select 'LORENZOD BROWN'
insert into b25 select 'LORENZOF BROWN'

create table b26 (char_omitted nvarchar(50))
insert into b26 select 'A'
insert into b26 select 'C'
insert into b26 select 'D'
insert into b26 select 'F'
insert into b26 select 'lo'

expected rusult :renzo brown

Result after applying the code: renz brwn
the goal was to omit "lo"
in practis all "o" was omitted

harishgg1 · March 13, 2024, 2:16am

hi

i tried using the cursor approach .. works

DECLARE @name NVARCHAR(50), @char_omitted NVARCHAR(50) , @output NVARCHAR(50)
    
DECLARE vendor_cursor CURSOR FOR   select * from b25 
  
OPEN vendor_cursor  
  
FETCH NEXT FROM vendor_cursor   INTO @name
  
WHILE @@FETCH_STATUS = 0  
BEGIN  

	DECLARE product_cursor CURSOR FOR   select * from b26
  
    OPEN product_cursor  

    FETCH NEXT FROM product_cursor INTO @char_omitted  
    
  set @output = @name

    WHILE @@FETCH_STATUS = 0  
    BEGIN  
  
       set @output  = replace(@output,@char_omitted,'')

        FETCH NEXT FROM product_cursor INTO @char_omitted  
        END  
  
    CLOSE product_cursor  
    DEALLOCATE product_cursor  
  		select @output
    FETCH NEXT FROM vendor_cursor   INTO @name
END   
CLOSE vendor_cursor;  
DEALLOCATE vendor_cursor;

tamiml · March 14, 2024, 2:14pm

I apologize for not being clear enough .Below is theexpected result

spenceredwin · March 15, 2024, 4:51am

I have also this issue but it helps me. Thank You

harishgg1 · March 15, 2024, 7:02am

hi

hope this helps

solution code

DECLARE @name NVARCHAR(50), @char_omitted NVARCHAR(50) , @output NVARCHAR(50)

create table #Output(name varchar(100) , correct_name varchar(100) )

DECLARE vendor_cursor CURSOR FOR select * from b25

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE product_cursor CURSOR FOR   select * from b26

OPEN product_cursor  

FETCH NEXT FROM product_cursor INTO @char_omitted

set @output = @name

WHILE @@FETCH_STATUS = 0  
BEGIN  

   set @output  = replace(@output,@char_omitted,'')

    FETCH NEXT FROM product_cursor INTO @char_omitted  
    END  

CLOSE product_cursor  
DEALLOCATE product_cursor  
	insert into #Output select @name,@output
FETCH NEXT FROM vendor_cursor   INTO @name

END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

select * from #Output
drop table #Output

tamiml · March 15, 2024, 2:13pm

thank you very much.

gdl · March 16, 2024, 9:38pm

If we wanted to do everything in memory, we could eliminate the cursors and use tables B25 and B26 if only they were equipped with an ID column of type IDENTITY(1,1).
Otherwise, you could use table-type variables as in the example below.

DECLARE @B25Array TABLE (
  IDX INTEGER IDENTITY(1,1)
  , NAME VARCHAR(50)
)
INSERT INTO @B25Array(NAME) SELECT NAME FROM b25
--
DECLARE @B26Array TABLE (
  IDX INTEGER IDENTITY(1,1)
  , CHAR_OMITTED VARCHAR(50)
)
INSERT INTO @B26Array(CHAR_OMITTED) SELECT char_omitted FROM b26
--
DECLARE @CORRECT_NAMES_TABLE AS TABLE
(
	NAME VARCHAR(50)
	, CORRECT_NAME VARCHAR(50)
)
--
DECLARE @NAME VARCHAR(50)
DECLARE @CORRECT_NAME VARCHAR(50) = ''
DECLARE @CHAR_OMITTED VARCHAR(50)
DECLARE @I INT
DECLARE @J INT
DECLARE @B25Max INT = (SELECT MAX(IDX) FROM @B25Array)
DECLARE @B26Max INT = (SELECT MAX(IDX) FROM @B26Array)
--
SET @I = 1
WHILE @I <= @B25Max
BEGIN
	SET @NAME = (SELECT NAME FROM @B25Array WHERE IDX = @I)
	SET @CORRECT_NAME = @NAME
	SET @J = 1
	WHILE @J <= @B26Max
	BEGIN
		SET @CHAR_OMITTED = (SELECT CHAR_OMITTED FROM @B26Array WHERE IDX = @J)
		SET @CORRECT_NAME = REPLACE(@CORRECT_NAME, @CHAR_OMITTED, '')
		SET @J = @J + 1
	END
	INSERT INTO @CORRECT_NAMES_TABLE(NAME, CORRECT_NAME) VALUES (@NAME, @CORRECT_NAME)
	SET @I = @I + 1
END
SELECT * FROM @CORRECT_NAMES_TABLE

gdl · March 18, 2024, 4:58pm

If you wanted to exploit the STRING_AGG function, you could treat the two tables as if they were stream files, as I thought of doing in this example:

declare @separator char(1) = char(255)

declare @names varchar(max) = (select string_agg(cast(name as varchar(max)), @separator) + @separator from b25)
declare @name varchar(50)
declare @pos_name int = charindex(@separator, @names)

declare @chars_omitted varchar(max) = (select string_agg(cast(char_omitted as varchar(max)), @separator) + @separator from b26)
declare @char_omitted varchar(50)
declare @pos_char_omitted int

declare @correct_name varchar(50)

declare @start_name int = 1
declare @start_omitted int

declare @correct_names as table
(
	name varchar(50)
	, correct_name varchar(50)
)

while (@pos_name > 0) begin
	set @name = substring(@names, @start_name, @pos_name - @start_name)
	set @correct_name = @name
	set @pos_char_omitted = charindex(@separator, @chars_omitted)
	set @start_omitted = 1
	while (@pos_char_omitted > 0) begin
		set @char_omitted = substring(@chars_omitted, @start_omitted, @pos_char_omitted - @start_omitted)
		set @correct_name = replace(@correct_name, @char_omitted, '')
		set @start_omitted = @pos_char_omitted + 1
		set @pos_char_omitted = charindex(@separator, @chars_omitted, @start_omitted)
	end
	insert into @correct_names (name, correct_name) values (@name, @correct_name)
	set @start_name = @pos_name + 1
	set @pos_name = charindex(@separator, @names, @start_name)
end
-- Output
select * from @correct_names

However, it would be interesting if we could solve this problem without using iterative instructions.