SQL script to create mask

Asked

Viewed 4,839 times

1

I’m with a client database that with some data without mask for use in my system that is the field NCM;

The same has 8 digits ( ex.: 0000.00.00), However the same in my system this maskless "00000000" I need to simply put points 4,2,2 but I am not able to mount an Update to do this.

Using this select I can visualize 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

However I cannot generate an Update to update the data in question;

Remembering that the Field NCM(Varchar 20) this one with the name Classificacao_fiscal and the table name is c000025;

sql sql-server

  • Either pro SQL Server or Mysql?

  • @Guilhermeneitzelmachado: How the NCM column is declared?

  • COLUMN Ncm is defined as "Classificaca_fiscal" and the table with the name "c000025"

  • @rray the script in question would be for SQL

  • 4

    The database appears in order. Formatting characters should normally not be stored, but placed in the view.

  • 1

    Do not format data in the database environment. It is a bad practice, creates processing in the database without need. Formatting data is not the responsibility of the bank. It makes the system more difficult to maintain. Do on the application layer, it was made for this. It will make it easier, faster, it is testable, and you will have more features. It’s my 50 cents.

Show 1 more comment

3 answers

1

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

0

It is possible to arrive at the result with stuff.

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

Operating here: SQL Fiddle

0

Look at this link, I believe that this function does exactly what you need.

Below is citei function on 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', '####.##.##')

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.