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? — , Mar 28 '18 at 13:38
What sql platform are you using? What is the error message, if any, returned? — Wes H, Mar 28 '18 at 13:38
Error in FROM clause: near 'SELECT'. Unable to parse query text. — Cátia Matos, Mar 28 '18 at 13:39
Where does fatura come from? Please eloborate on the logic a little. — SQL_M, Mar 28 '18 at 13:46
I have in column Cust.[Document Type] the number 2 (by running the previous query) and I want to show factura because the number 2 means factura understand? — Cátia Matos, Mar 28 '18 at 13:49

score 1 · Answer 1 · answered Mar 28 '18 at 13:48

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')

score 0 · Answer 2 · answered Mar 28 '18 at 13:51

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 '18 at 13:51

shamim

6,640
20
85
151

Assuming the data type isn't `INT`. – BJones Mar 28 '18 at 13:53
After join you have value and you know the destination column type, So you can cast it. – shamim Mar 28 '18 at 13:55
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 Mar 28 '18 at 14:00
I alter the Update table for Cust and the Set field to Cust.[Document Type] – Cátia Matos Mar 28 '18 at 14:00
What is your document type? What type of data you want to store in this column. Please check hope types are not mis-match – shamim Mar 28 '18 at 14:31

SQL replace int in string by text

2 Answers2