UPDATE values in a string position begin or middle or end of string

Transact-SQL
1

Hi,
I'ld like to remove a value from a string by updating the field.
The value can be at the begin or end or anywhere in the string because the field is feed with multivalues like this : ;1; if only one value, ;1;15; if two or more values.
The purpose in my case is to remove this value ;15; (easy if only one value because i only have to update with '' (blank) but if two values or more i have to do this ...
;1;15; should become ;1; and if ;15;1; it shoud become ;1; as well so i have to remove in fact always 15; except when this is the only value in the field

Could you improve my code ?
UPDATE ADMINEFFICY.CONT$MEMBERSHIP
SET CONT$MEMBERSHIP.F_CODE_MAILING = ???
WHERE CONT$MEMBERSHIP.K_CONTACT NOT IN (SELECT RPUBLCONT.K_CONTACT FROM ADMINEFFICY.R_PUBL_CONT AS RPUBLCONT WHERE RPUBLCONT.K_PUBLICATION = 2279) AND
CONT$MEMBERSHIP.F_CODE_MAILING LIKE ('%;15;%')

Thank you
Didier

2

You just want to remove "15" from anywhere in a comma separated list?

This perhaps:

DECLARE @TEMP SameDataTypeAndSizePlus2AsColumn!!
UPDATE U
SET @TEMP = REPLACE(';' + U.F_CODE_MAILING + ';', ',15,', ';'),
    U.F_CODE_MAILING = STUFF(STUFF(@TEMP, LEN(@TEMP), 1, ''), 1, 1, '')
FROM ADMINEFFICY.CONT$MEMBERSHIP AS U
WHERE ...

Note that is the column value is exactly "15" it will be converted to NULL, not an empty string. If you need empty string the COALESCE the result with ''

1 Like
3

Hi Kristen
Not exactly i want to remove 15; if more than one value and ;15; if only one value in the field.
Cheers
Didier

4

OK, that's a better specification. The code I posted should do that (with the NULL / empty-string caveat that I mentioned)

5

Just spotted that your separator is "semi-colon" and not "comma". I've edted the code to use that separator

6

That's probably not going to work.

Presumably you need to match all these

15
15;
x;15;x
x;15
x;15;

if you have a smaller set of "possibles" that will help with the matching

7

Me again!

If you wind up with redundant separators you might consider wrapping the column replace statement with

REPLACE(
        TheCurrentFormula
        , ';;', ';')

although that will replace any doubled-up-separators, not just the ones resulting from this replace.

As with all these things, such "lists" within a single column are much better served by having a Child Table instead. Much easier to, accurately and without ambiguity, query and manage the data.

8

Hi the set of possibilities is in this case limited, i've checked before
;15;
;3;15;
;15;3;

So i would get after update
'' (blank)
;3;
;3;

It this clear for you as when we put a value in the field when empty it automatically adds ; before and after value and if you put a second value it put only the value + ; behind
Separator is ; and not , (comma)

Cheers
Didier

9

Hi Kirsten
Done this with two queries :slight_smile:
One that removes ;15; from all strings and replaces it with ;
And one that removes the ; from all fields that only contains ;

Thanks for your help much appreciated :slight_smile:
Cheers
Didier

CODE :
UPDATE ADMINEFFICY.CONT$MEMBERSHIP
SET CONT$MEMBERSHIP.F_CODE_MAILING = REPLACE (CONT$MEMBERSHIP.F_CODE_MAILING,';15;',';')
--SELECT K_CONTACT, F_CODE_MAILING
--FROM ADMINEFFICY.CONT$MEMBERSHIP
WHERE CONT$MEMBERSHIP.K_CONTACT NOT IN (SELECT RPUBLCONT.K_CONTACT FROM ADMINEFFICY.R_PUBL_CONT AS RPUBLCONT WHERE RPUBLCONT.K_PUBLICATION = 2279) AND
CONT$MEMBERSHIP.F_CODE_MAILING LIKE ('%;15;%')

UPDATE ADMINEFFICY.CONT$MEMBERSHIP
SET CONT$MEMBERSHIP.F_CODE_MAILING = NULL
--SELECT K_CONTACT, F_CODE_MAILING
--FROM ADMINEFFICY.CONT$MEMBERSHIP
WHERE CONT$MEMBERSHIP.K_CONTACT NOT IN (SELECT RPUBLCONT.K_CONTACT FROM ADMINEFFICY.R_PUBL_CONT AS RPUBLCONT WHERE RPUBLCONT.K_PUBLICATION = 2279) AND
CONT$MEMBERSHIP.F_CODE_MAILING = ';'

10

How about this?:

UPDATE ADMINEFFICY.CONT$MEMBERSHIP
SET CONT$MEMBERSHIP.F_CODE_MAILING = REPLACE(CONT$MEMBERSHIP.F_CODE_MAILING, ';15;',
    CASE WHEN LEN(CONT$MEMBERSHIP.F_CODE_MAILING) = 4 THEN '' ELSE ';' END)
WHERE CONT$MEMBERSHIP.K_CONTACT NOT IN (SELECT RPUBLCONT.K_CONTACT FROM ADMINEFFICY.R_PUBL_CONT AS RPUBLCONT WHERE RPUBLCONT.K_PUBLICATION = 2279) AND
CONT$MEMBERSHIP.F_CODE_MAILING LIKE ('%;15;%')
1 Like
11

Hi Scott,
You've got it :smile:
Tested when putting ;15; everywhere in the string and fully functionnal
Thank you very much to you and Kristen

Have a nice day

PS : how do you put your code like you did ? i'm a newbee here

12

If your data reliable has leading and trailing ';' then you can simplify the REPLACE to

NullIf(REPLACE(F_CODE_MAILING, ';15;', ';'), ';')

This will work regardless of the LEN of the search string

Here's hoping!

I've never seen a system like this that maintained it precisely and for all time ...

Just to check:



; WITH MyCTE AS
(
SELECT	[IssueCode] = CASE WHEN F_CODE_MAILING = '' THEN 'Blank'
			WHEN F_CODE_MAILING LIKE '[^;]%;' THEN 'Leading'
			WHEN F_CODE_MAILING LIKE ';%[^;]' THEN 'Trailing'
			WHEN F_CODE_MAILING NOT LIKE ';%;' THEN 'Both ends'
			WHEN F_CODE_MAILING LIKE '%[^;0-9]%' THEN 'Illegal char'
			WHEN F_CODE_MAILING LIKE '%;;%' THEN 'Double ;;'
			ELSE NULL
			END,
	F_CODE_MAILING
FROM	ADMINEFFICY.CONT$MEMBERSHIP
WHERE	F_CODE_MAILING IS NOT NULL
)
SELECT	[Count]=COUNT(*), IssueCode, [Min]=MIN(F_CODE_MAILING), [Max]=MAX(F_CODE_MAILING)
FROM	MyCTE
WHERE	1=1
	AND IssueCode IS NOT NULL
--
GROUP BY IssueCode
ORDER BY IssueCode

    ```sql
    [your code here]
    ```
1 Like