none
Column Data Format(Function) RRS feed

  • Question

  • Hi All,
    I have a requirement from client, Format column data based on input format. Format value coming from DATAFORMAT Table(Value is Dynamic).If we change value in DATAFORMAT table, Data Should be change in as per format.

    Ex: Source Data : ApDUO8952996O898  OUT PUT DATA - Ap-DUO8-952996.O898 

    *******************************************************************************

    IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('ProductFormat'))
    BEGIN;
        DROP TABLE [ProductFormat];
    END;
    GO

    CREATE TABLE [ProductFormat] (
        [PRODID] INT NULL,
        [PRODNUM] CHAR(16) NULL,
        [USERNAME] VARCHAR(20) NULL)
    GO

    INSERT INTO ProductFormat([prodid],[prodnum],[username]) 
    VALUES(1,'DuisAv8569','AAA'),
    (2,'ApDUO8952996O898','BBB'),
    (3,'GFTYHVGFR3940','CCC'),
    (4,'ApEuRd4003726','DDD'),
    (5,'ApFacilisisRd12','EEE'),
    (6,'PulvinarRoad969','FFF'),
    (7,'POBoxId7243033','GGG'),
    (8,'ApUtSt6614403','HHH'),
    (9,'ApNislStreet4719','III'),
    (10,'CursusRoad90823','JJJ');

    //
    IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('DATAFORMAT'))
    BEGIN;
        DROP TABLE [DATAFORMAT];
    END;
    GO

    CREATE TABLE DATAFORMAT (
    FormatId Int,
    FORMATDATA varchar(30)
    )
    INSERT INTO DATAFORMAT VALUES(1,'33 ##-####-##.####')

    /*Query I Tried*/
    DECLARE @search VARCHAR(5)
    SET @search = '-'

    DECLARE @Stringname VARCHAR(40)
    SET @Stringname = (SELECT TOP 1 RTRIM(SUBSTRING (RTRIM(FORMATDATA), CHARINDEX(' ', RTRIM(FORMATDATA) ) + 1, LEN(RTRIM(FORMATDATA)))) AS [FormatString]
    FROM DATAFORMAT
                        WHERE FormatId=1 )

    DECLARE @init INT
    SET @init = 1
    DECLARE @CharaterPosition INT
    DECLARE @table TABLE (id INT IDENTITY(1,1),CHARPOSITION INT)

    WHILE (@init <= LEN(@Stringname))
    BEGIN
    SET @CharaterPosition = (SELECT CHARINDEX(@search, @Stringname, @init))

    IF (@CharaterPosition != 0)
    BEGIN
    INSERT INTO @table
    SELECT @CharaterPosition

    SET @init = @CharaterPosition + 1
    END
    ELSE IF (@CharaterPosition = 0)
    BEGIN
    BREAK
    END
    END

    --SELECT * FROM @table

    DECLARE @Inputstart  INT= (SELECT TOP 1 CHARPOSITION FROM @table WHERE id=1)
    DECLARE @Inputsecond INT= (SELECT Top 1 CHARPOSITION FROM @table WHERE id=2)
    DECLARE @InputThird  INT= (SELECT Top 1 CHARPOSITION FROM @table WHERE id=3)
    DECLARE @InputRight  INT= (SELECT CHARINDEX('.',REVERSE(@Stringname),1))

    --SELECT LEN(RTRIM(@Stringname))

    DECLARE @prodnum CHAR(20)='ApDUO8952996O898'
    DECLARE @LENSRCST VARCHAR(20)= (SELECT LEFT(RTRIM(@prodnum),LEN(RTRIM(@prodnum))-LEN(RIGHT(RTRIM(@prodnum),4))))

    SELECT LEFT(CAST(RTRIM(@prodnum) AS VARCHAR(50)),@Inputstart-1)+'-'+SUBSTRING(CAST(RTRIM(@prodnum) AS VARCHAR(50)),@Inputstart,@Inputsecond-(@Inputstart+1))--+SUBSTRING(CAST(RTRIM(@prodnum) AS VARCHAR(50)),@Inputstart+@Inputsecond+1,@InputThird)
    +'.'+RIGHT(RTRIM(@prodnum),@InputRight-1)
    FROM ProductFormat
    WHERE USERNAME='AAA'

    /*OUT PUT DATA*/


    **********************************************************************************************************

    TThanks in Advance...

    Sunday, August 2, 2020 6:21 AM

All replies

  • And what are the exact rules for the format?

    I spent quite some time on composing a solujtion, but it was not until I was done, that I noticed that .#### means that the dot should be before the last four characters, and thus two ## that comes before the dot apparently are a rubber band. In this particualr format. And what does the 33 in the beginning mean?

    I don't think that I want to try to extend my solution to try to accommdate this. Ratrher, I return to my initial reaction that I don't want to do this in T-SQL. If I really would do this in SQL Server, I would use the CLR or possibly in Python on SQL 2017 or later. But since it is a presentation thing, I think it should be pushed to the presenation layer.

    Not that you have much use for it, but I dump my solution below. Note that it requires SQL 2017. It also requires a table of numbers, see
    http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum (only read section 8.1).

    CREATE TABLE [ProductFormat] (
        prodid INT NULL,
        prodnum CHAR(16) NULL,
        username VARCHAR(20) NULL)
    GO

    INSERT INTO ProductFormat([prodid],[prodnum],[username]) 
    VALUES(1,'DuisAv8569','AAA'),
    (2,'ApDUO8952996O898','BBB'),
    (3,'GFTYHVGFR3940','CCC'),
    (4,'ApEuRd4003726','DDD'),
    (5,'ApFacilisisRd12','EEE'),
    (6,'PulvinarRoad969','FFF'),
    (7,'POBoxId7243033','GGG'),
    (8,'ApUtSt6614403','HHH'),
    (9,'ApNislStreet4719','III'),
    (10,'CursusRoad90823','JJJ');

    IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('DATAFORMAT'))
    BEGIN;
        DROP TABLE [DATAFORMAT];
    END;
    GO

    CREATE TABLE DATAFORMAT (
    FormatId Int,
    FORMATDATA varchar(30)
    )
    INSERT INTO DATAFORMAT VALUES(1,'33 ##-####-##.####')

    go
    WITH prodchars AS(
       SELECT P.prodid, substring(P.prodnum, n.n, 1) as char, P.username, n.n AS charpos
       FROM   ProductFormat P
       JOIN   Numbers n ON n.n BETWEEN 1 AND len(P.prodnum)
    ), dataformatseqs AS (    SELECT substring(D.FORMATDATA, n.n,                     CASE WHEN n.n = len(D.FORMATDATA)
                             THEN  1
                             ELSE  charindex('#', D.FORMATDATA, n.n + 1) - n.n
                        END) AS charseq, n.n AS pos
       FROM   DATAFORMAT D
       JOIN   Numbers n ON n.n BETWEEN 1 AND len(D.FORMATDATA)

    ), filterhash AS (
       SELECT charseq, row_number() OVER(ORDER BY pos) AS hashpos,
              IIF(len(charseq) > 1, substring(charseq, 2, len(charseq)), '') AS extra
       FROM   dataformatseqs
       WHERE  charseq LIKE '#%'
    )
    SELECT p.prodid, string_agg(p.char + isnull(f.extra, ''), '') WITHIN GROUP (ORDER BY p.charpos), p.username
    FROM   prodchars p LEFT   JOIN  filterhash f ON p.charpos = f.hashpos
    GROUP  BY p.prodid, p.username
    go
    DROP TABLE ProductFormat
    DROP TABLE DATAFORMAT


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, August 2, 2020 10:04 PM
  • Hi BHVS,

    I made a small modification on the PRODNUM column's definition.

    Please refer below and check whether it is helpful to you. Thanks.

    IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('ProductFormat'))
    BEGIN;
        DROP TABLE [ProductFormat];
    END;
    GO
    
    CREATE TABLE [ProductFormat] (
        [PRODID] INT NULL,
        [PRODNUM] VARCHAR(30) NULL,
        [USERNAME] VARCHAR(20) NULL)
    GO
    
    INSERT INTO ProductFormat([prodid],[prodnum],[username]) 
    VALUES(1,'DuisAv8569','AAA'),
    (2,'ApDUO8952996O898','BBB'),
    (3,'GFTYHVGFR3940','CCC'),
    (4,'ApEuRd4003726','DDD'),
    (5,'ApFacilisisRd12','EEE'),
    (6,'PulvinarRoad969','FFF'),
    (7,'POBoxId7243033','GGG'),
    (8,'ApUtSt6614403','HHH'),
    (9,'ApNislStreet4719','III'),
    (10,'CursusRoad90823','JJJ');
    
    IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('DATAFORMAT'))
    BEGIN;
        DROP TABLE [DATAFORMAT];
    END;
    GO
    
    CREATE TABLE DATAFORMAT (
    FormatId Int,
    FORMATDATA varchar(30)
    )
    INSERT INTO DATAFORMAT VALUES(1,'33 ##-####-##.####')
    
    declare @formart varchar(100)
    declare @n int=1
    
    set @formart=(select SUBSTRING(FORMATDATA,CHARINDEX('#',FORMATDATA),len(FORMATDATA)-CHARINDEX('#',FORMATDATA)+1)  
    from DATAFORMAT
    where FormatId=@n)
    
    declare @n1 int
    declare @n2 int
    declare @n3 int
    declare @n4 int
    
    DECLARE @search VARCHAR(5)
    SET @search = SUBSTRING(@formart,PATINDEX('%[^#]%', @formart),1)--'-'
    
    set @n1=CHARINDEX(@search,@formart)-1 --len of first part before first '-'
    set @n2=CHARINDEX(@search,@formart,CHARINDEX(@search,@formart)+1)-CHARINDEX(@search,@formart)-1  --len of second part before '-'
    set @n3=CHARINDEX('.',REVERSE(@formart))-1  --len of last part after '.'
    set @n4=CHARINDEX(@search,@formart,CHARINDEX(@search,@formart)+1)  --the position of second '-'
    
    SELECT PRODID,
    CASE WHEN LEN(PRODNUM)<=(@n1+@n2+@n3) THEN 
    LEFT(PRODNUM,@n1)+@search+SUBSTRING(PRODNUM,@n1+1,LEN(PRODNUM)-(@N1+@N3))+'.'+RIGHT(PRODNUM,@n3)
    WHEN LEN(PRODNUM)>(@n1+@n2+@n3) THEN 
    LEFT(PRODNUM,@n1)+@search+SUBSTRING(PRODNUM,@N1+1,@N2)+@search+SUBSTRING(PRODNUM,@n1+@n2+1,LEN(PRODNUM)-(@N1+@N2+@N3))+'.'+RIGHT(PRODNUM,@n3)
    END PRODNUM,USERNAME FROM ProductFormat
    
    DROP TABLE ProductFormat
    DROP TABLE DATAFORMAT
    
    /*
    PRODID	PRODNUM	USERNAME
    1	Du-isAv.8569	AAA
    2	Ap-DUO8-952996.O898	BBB
    3	GF-TYHV-GFR.3940	CCC
    4	Ap-EuRd-400.3726	DDD
    5	Ap-Faci-lisis.Rd12	EEE
    6	Pu-lvin-arRoa.d969	FFF
    7	PO-BoxI-d724.3033	GGG
    8	Ap-UtSt-661.4403	HHH
    9	Ap-Nisl-Street.4719	III
    10	Cu-rsus-Road9.0823	JJJ
    */
    

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, August 3, 2020 7:32 AM
  • Hi BHVS,

    Could you please provide any update about this?

    Please remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

    Thank you for understanding!

    Best regards,
    Melissa


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Tuesday, August 4, 2020 7:32 AM