0

I have an nvarchar field in my database called CatCustom which contains comma-separated 5-character codes. It can contain as little as one code, or as many as 20 codes, separated by commas.

Right now, I use this query to add a new 5-character code to the field in given records (in this case the new code is LRR01):

UPDATE dbo.Sources
SET    CatCustom = CONCAT_WS(', ', RTRIM(CatCustom), 'LRR01')
WHERE  SourceID IN (1,2,3,4,5,8,9,44,63,45,101,102,222,344)

I need to add to this though: I need the record to be updated only if that 5-character code doesn't already exist somewhere in the CatCustom field, to ensure that code is not in there more than once.

How would I accomplish this?

EDIT: I really don't understand how this can be considered a duplicate of the suggested thread. This is a VERY specific case and has nothing to do with creating stored procedures and or variables. The alleged duplicated thread does not really help me - sorry.

Stpete111
  • 3,109
  • 4
  • 34
  • 74
  • 1
    It is basically a dup. Looks for `',' + SourceID + ',' like '%' + 'LRR01' + '%'` This is probably not a great design though. – shawnt00 Jul 25 '18 at 19:41
  • 1
    The answer to the marked duplicate shows you how to check to see if there is a certain string of characters within a larger string. This answer should address your question, which makes your question a duplicate. – Tab Alleman Jul 25 '18 at 19:45
  • 1
    create a table function which will accept `CatCustom` as parameter, inside the function convert that comma separated string to table with single column and then use that function in the Where clause of your query to see if new code exists or not. Another approach is multiple like conditions. – Vinit Jul 25 '18 at 19:46
  • 1
    By far the best solution would be to stop storing delimited data. It violates 1NF and is a serious pain to work with. Normalize your data and you have no issue at all. – Sean Lange Jul 25 '18 at 19:54
  • 3
    I think `where CatCustom not like '%LRR01%'` to your update clause would work. @SeanLange comments are very true here too. Also, I don't think this is a duplicate for what it's worth, even though the `charindex` method in a where clause is another way of writing this `like` clause – S3S Jul 25 '18 at 19:55
  • I am not totally clear what you are trying to do here. Are you able to fix the design of this and normalize it properly? Or are you stuck with this design? – Sean Lange Jul 25 '18 at 20:00
  • @SeanLange I hear you and normalization is definitely on the roadmap. Thanks! – Stpete111 Jul 25 '18 at 20:05
  • 1
    OK great!!! I am happy to help you with that. once this properly normalized your query will be super simple. Let's start with the current table structure and 2 or 3 rows of sample data. It will need to be broken into at least 2 tables and there are plenty of tools available to help break the data apart into the new structures. – Sean Lange Jul 25 '18 at 20:07
  • What happened to you? – Sean Lange Jul 25 '18 at 21:12
  • 1
    My two cent: `(1)` @Vinit the OP uses the function CONCAT_WS which mean that he is using SQL Server 2017 and above. Therefore there is no reason for him to `create a table function...` since there is a built-in function for this from 2016 - STRING_SPLIT. `(2)` I agree with Sean Lange on preferring not to use this structure, but not because I care about Database normalization. The reason that important is performance, and duplication. MSSQL have poor performance in parsing strings. It is optimized to work with SET of data. `(3)` scsimon this will not work in this case, check shawnt00 solution – Ronen Ariely Jul 26 '18 at 02:56
  • 1
    @RonenAriely, thanks for the info on STRING_SPLIT, good to know that :) – Vinit Jul 26 '18 at 17:05
  • @SeanLange I'm still here! Should we take this discussion to chat? Thanks again! – Stpete111 Jul 26 '18 at 19:17

2 Answers2

1
UPDATE dbo.Sources
SET
    CatCustom = CONCAT_WS(', ', RTRIM(CatCustom), 'LRR01')
WHERE
    SourceID IN (1,2,3,4,5,8,9,44,63,45,101,102,222,344)
    AND CatCustom NOT LIKE '%LRR01%';
critical_error
  • 6,306
  • 3
  • 14
  • 16
  • 1
    this may not work if existing CatCustom has a code like 'LRR010'. – Vinit Jul 26 '18 at 17:08
  • 1
    Going by his statement, I think that is what he wants: "I need the record to be updated only if that 5-character code doesn't already exist **somewhere** in the CatCustom field..." Unless of course, he means by each source id, then yes, you'd be correct. – critical_error Jul 26 '18 at 17:27
1

Use STRING_SPLIT function to split the comma separated list and then add Not Exist condition in the WHERE clause like below

UPDATE dbo.Sources
SET    CatCustom = CONCAT_WS(', ', RTRIM(CatCustom), 'LRR01')
WHERE  SourceID IN (1,2,3,4,5,8,9,44,63,45,101,102,222,344)
       AND NOT EXISTS (SELECT 1 FROM STRING_SPLIT(CatCustom, ',')  where value = 'LRR01')
Vinit
  • 2,540
  • 1
  • 15
  • 22