-1

For example, if I had a condition I needed to be met, then I need another thing done to the column values, how would I go about doing that?

    CAST(
    CASE WHEN TRY_CAST(CON_ORDERS_MIN.CONCAT_ITEM AS numeric) = o.ORDER_ID
        THEN NULL
    WHEN CHARINDEX(CAST(o.ORDER_ID AS nvarchar), CON_ORDERS_MIN.CONCAT_ITEM) > 0
    --CONDITION 1
        THEN REPLACE(CON_ORDERS_MIN.CONCAT_ITEM, CAST(o.ORDER_ID AS nvarchar), '')
    --CONDITION 2
        THEN replace(ltrim(rtrim(replace(replace(replace(replace(col, ',', '><'), '<>', ''), '><', ','), ',', ' '))), ' ', ',')
    ELSE CON_ORDERS_MIN.CONCAT_ITEM
    END AS VARCHAR
)AS EXAMPLE

I'm asking "when this number is found in this list of numbers, then remove that number and get rid of the commas." So when it's found, replace that with nothing and clean up the commas. And I don't really understand the long nested replacey thing. In fact, it hurts my brain to look at it. Does anyone have a better way to do this?

Data example:

ID_TB_SEARCHED     CSV_IDS
1234567            1234567, 8900123, 12349786

What i am trying to achieve:

ID_TB_SEARCHED     CSV_IDS
1234567            8900123, 12349876

Edit: Also, i know that the second replace function isn't correct. I just copied it and pasted.

hanbanan
  • 81
  • 5
  • 1
    Fix your data model so you are not storing numbers as strings. And you are not storing multiple values in a string column. And -- if these are ids -- then foreign key relationships are properly declared. – Gordon Linoff Apr 27 '20 at 17:55
  • @GordonLinoff I have no control on what the columns' datatypes are (by default, i guess?). I don't know how else to compare two records that don't have the same datatype. Also, all the IDs are unique. – hanbanan Apr 27 '20 at 17:57
  • Please tag your question with the specific database that you're using. [Why should I “tag my RDBMS”?](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) – Eric Brandt Apr 27 '20 at 18:20
  • added it, but i thought it was a simpler question? @EricBrandt – hanbanan Apr 27 '20 at 18:22
  • @GordonLinoff thanks for y'all's help. i just put the function in the other function and went with that! – hanbanan Apr 27 '20 at 18:47

1 Answers1

0

It finally sank in that I should just combine the two ha!

replace(ltrim(rtrim(replace(replace(replace(replace(REPLACE(CON_ORDERS_MIN.CONCAT_ITEM, CAST(ord.ORDER_ID AS nvarchar), ''), ',', '><'), '<>', ''), '><', ','), ',', ' '))), ' ', ',')
hanbanan
  • 81
  • 5