SQL replace int in string by text

Question

I'm trying to do a query but in my result I have a column with the number '2' which I want to be replaced by 'factura'.

How can I do this?? I want to replace Cust.[Document Type] column

   SELECT Detail.[Entry No_],
           'Cliente' AS Tipo,
           Cust.[Customer No_] AS Cliente,
           Detail.[Posting Date] AS DATA,
           Detail.[Document No_] AS Documento,
           Detail.[Amount (LCY)] AS Valor,
           Cust.[Document Type] AS LiqPorTipo,
           Cust.[Document No_] AS LiqPorNDocumento,
           '97' AS Conta,
           'MR' AS Loja,
           'SUPER' AS Utilizador,
           'MR01' AS POS
    FROM dbo.MBS_tabela_21Detailed_NAV16 AS Detail
    INNER JOIN dbo.MBS_tabela_21_NAV16 AS Cust ON Detail.[Cust_ Ledger Entry No_] = Cust.[Entry No_]
    INNER JOIN dbo.Integracao_Periodo_NAV16 AS Integr ON YEAR(Detail.[Posting Date]) = Integr.Ano
    AND MONTH(Detail.[Posting Date]) = Integr.Mes
    WHERE (Detail.[Document No_] LIKE '%REC%'
           OR Detail.[Document No_] LIKE '%L%')
      AND (Detail.[Entry Type] = 2)
      AND (Cust.[Global Dimension 1 Code] = 'LMR')

this results in

359229  Cliente 503392154   2018-03-23 00:00:00.000 1803PAGLEITE37  -2064,62000000000000000000  2   MRVFFT1800012   97  MR  SUPER   MR01

and i want to have

359229  Cliente 503392154   2018-03-23 00:00:00.000 1803PAGLEITE37  -2064,62000000000000000000  fatura  MRVFFT1800012   97  MR  SUPER   MR01

That is invalid standard SQL. Which DBMS product are you using? — user330315
– user330315, Commented Mar 28, 2018 at 13:38
What sql platform are you using? What is the error message, if any, returned? — Wes H
– Wes H, Commented Mar 28, 2018 at 13:38
Error in FROM clause: near 'SELECT'. Unable to parse query text. — Cátia Matos
– Cátia Matos, Commented Mar 28, 2018 at 13:39

LukaszBalazy · Accepted Answer · 2018-03-28 13:48:52Z

Your query is missing "Detail.[Entry Type] column". Comparing expected query result and your query result I assume you would like to use either case or create dictionary table to join it.

Best option would be to create additional table and store there all key-value translations.

So you could have 2 - factura

And join it in your query.

If not you should do like this:

 SELECT Detail.[Entry No_],
           'Cliente' AS Tipo,
           Cust.[Customer No_] AS Cliente,
           Detail.[Posting Date] AS DATA,
           Detail.[Document No_] AS Documento,
           Detail.[Amount (LCY)] AS Valor,
           case when Cust.[Document Type] = 2 then 'factura' else '' end  AS LiqPorTipo,
           Cust.[Document No_] AS LiqPorNDocumento,
           '97' AS Conta,
           'MR' AS Loja,
           'SUPER' AS Utilizador,
           'MR01' AS POS
    FROM dbo.MBS_tabela_21Detailed_NAV16 AS Detail
    INNER JOIN dbo.MBS_tabela_21_NAV16 AS Cust ON Detail.[Cust_ Ledger Entry No_] = Cust.[Entry No_]
    INNER JOIN dbo.Integracao_Periodo_NAV16 AS Integr ON YEAR(Detail.[Posting Date]) = Integr.Ano
    AND MONTH(Detail.[Posting Date]) = Integr.Mes
    WHERE (Detail.[Document No_] LIKE '%REC%'
           OR Detail.[Document No_] LIKE '%L%')
      AND (Detail.[Entry Type] = 2)
      AND (Cust.[Global Dimension 1 Code] = 'LMR')

shamim · Accepted Answer · 2018-03-28 13:51:08Z

0

As you want to replace one column base on join result. Bellow structure may help you

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

Base on above structure your sql scritp is bellow.

UPDATE Detail
SET Detail.[Entry Type] = REPLACE(Detail.[Entry Type], '2', 'factura')

FROM dbo.MBS_tabela_21Detailed_NAV16 AS Detail
INNER JOIN dbo.MBS_tabela_21_NAV16 AS Cust
  ON Detail.[Cust_ Ledger Entry No_] = Cust.[Entry No_]
INNER JOIN dbo.Integracao_Periodo_NAV16 AS Integr
  ON YEAR(Detail.[Posting Date]) = Integr.Ano
  AND MONTH(Detail.[Posting Date]) = Integr.Mes

WHERE (Detail.[Document No_] LIKE '%REC%'
OR Detail.[Document No_] LIKE '%L%')
AND (Detail.[Entry Type] = 2)
AND (Cust.[Global Dimension 1 Code] = 'LMR')

answered Mar 28, 2018 at 13:51

shamim

6,78821 gold badges94 silver badges151 bronze badges

5 Comments

BJones Over a year ago

Assuming the data type isn't INT.

shamim Over a year ago

After join you have value and you know the destination column type, So you can cast it.

Cátia Matos Over a year ago

This isnt working... Column or expression 'Document Type' cannot be updated. This SQL statement type cannot be used in a view or function. Only a SELECT statement can be used.

Cátia Matos Over a year ago

I alter the Update table for Cust and the Set field to Cust.[Document Type]

shamim Over a year ago

What is your document type? What type of data you want to store in this column. Please check hope types are not mis-match

Collectives™ on Stack Overflow

SQL replace int in string by text

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related