SQL script to create mascara

Question

SQL script to create mascara

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I have a database of a client that has some unmasked data for use on my system which is the NCM ;

The same has 8 digits (eg 0000.00.00), but the same in my system is unmasked "00000000" I need to simply put the points 4.2,2 but I am not able to mount an Update to do this .

Using this select I can see the same with the Masks:

select codigo,substring(d.classificacao_fiscal from 1 for 4)||'.'||
       substring(d.classificacao_fiscal from 5 for 2)||'.'||
       substring(d.classificacao_fiscal from 7 for 2) as classificacao_fiscal

from c000025 d

But I can not generate an Update to update the data in question;

Remembering that the field NCM (Varchar 20) is named Classificacao_fiscal and the Name of the table is c000025 ;

sql sql-server

asked by anonymous 28.12.2016 / 18:06

3 answers

Ionic 2 - Menu items, first item does not appear Modal Bootstrap being closed

score 0 · Answer 1

Based on the principle that you are using SQL Server, that all records have 8 digits and that your NCM field is of type VARCHAR:

UPDATE TMP SET CODIGO = LEFT(CODIGO, 4) + '.' + SUBSTRING(CODIGO, 5, 2) + '.' + RIGHT(CODIGO, 2)

score 0 · Answer 2

See this link , I believe this function does exactly what it needs.

Below is function mentioned in the link.

CREATE FUNCTION fx_FormatUsingMask 
(    
    @input nvarchar(1000),
    @mask nvarchar(1000)
)
RETURNS nvarchar(1000)
AS
BEGIN    
    DECLARE @result nvarchar(1000) = ''
    DECLARE @inputPos int = 1
    DECLARE @maskPos int = 1
    DECLARE @maskSign char(1) = ''

    WHILE @maskPos <= Len(@mask)
    BEGIN
        set @maskSign = substring(@mask, @maskPos, 1)

        IF @maskSign = '#'
        BEGIN
            set @result = @result + substring(@input, @inputPos, 1)
            set @inputPos += 1
            set @maskPos += 1
        END
        ELSE
        BEGIN
            set @result = @result + @maskSign
            set @maskPos += 1
        END
    END
    -- Return the result of the function
    RETURN @result

END;

How to call the function:

SELECT [dbo].[fx_FormatUsingMask]('00000000', '####.##.##')

score 0 · Answer 3

0

You can reach the result with stuff .

update [tabela] set [coluna] = stuff(stuff([coluna],5,0,'.'),8,0,'.')

Running here: SQL Fiddle

06.07.2018 / 22:18