Sql-server – Recursively call STUFF for results in a table valued function

sql serversql-server-2012t-sql

This is a static query that kind of shows where I am trying to get

declare @Airbill VARCHAR(50) = '12345678912'
select stuff(stuff(@Airbill, 4, 0, '-'), 9, 0, '-')

But I need to make it more variable. I have the following varible:

DECLARE @AirbillMask VARCHAR(50) = '999-9999-9999'

Which can (and does) change. It is always '9's and '-'s. But the placement of them changes.

If @AirbillMask is 999-99-99-9999 my desired result would be 123-45-67-8912.
If @AirbillMask is 999-9999-9999 my desired result would be 123-4567-8912.

I am running dbo.FindPatternLocation(@AirbillMask, '-') that returns a table of dash locations.

I was planning to find a way to STUFF the parts of the @Airbill into it corresponding locations to produce a calculated field of FormattedAirbill, but I got stuck trying to iterate down into the STUFF calls.

Is there a way to call STUFF inside another STUFF call, over and over till I am out of rows?

I feel like a recursive CTE or cross apply could accomplish this, but I can't seem to wrap my head around it.

NOTE: I would prefer to do this without looping, if possible.

Best Answer

Your example is a big integer so assuming this is typical you can use FORMAT for this

declare @Airbill DECIMAL(38) = 12345678912


select FORMAT(@Airbill,   '000-0000-0000') AS A,
       FORMAT(@Airbill,  '000-00-00-0000') AS B,
       FORMAT(@Airbill, '00000-0000-0000') AS C,
       FORMAT(@Airbill, '#####-####-####') AS D;

Returns

+---------------+----------------+-----------------+---------------+
|       A       |       B        |        C        |       D       |
+---------------+----------------+-----------------+---------------+
| 123-4567-8912 | 123-45-67-8912 | 00123-4567-8912 | 123-4567-8912 |
+---------------+----------------+-----------------+---------------+

If it isn't possible to change the mask formats to use 0 or # instead of 9 it can obviously easily be done at run time with a single REPLACE of 9 to the desired character.

Related Solutions

Sql-server – Stuff function in T-SQL

In your first bullet you say the value becomes 00000010. That is incorrect. It actually becomes 000010 because of the RIGHT( , 6).

To clarify, compare the difference:

SELECT LEFT('00000010', 6), RIGHT('00000010', 6);

Results:

------  ------
000000  000010

Sql-server – Conditional WHERE Clause in a Table Valued Function

Like so :

CREATE FUNCTION [dbo].[DateRange] 
    (
        @StartDate date,
        @EndDate date,
        @Location varchar(25) = NULL,
        @Device varchar(25) = NULL
    ) 
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT *
        FROM MyTable
        WHERE 
          Date < @EndDate 
          AND Date > @StartDate 
          AND ( @Location IS NULL OR (@Location IS NOT NULL AND Location = @Location))
          AND ( @Device IS NULL OR (@Device IS NOT NULL AND Device = @Device ))

    )

IF the device and location columns can actually be NULL, you might want to consider this option though:

CREATE FUNCTION [dbo].[DateRange] 
    (
        @StartDate date,
        @EndDate date,
        @Location varchar(25) = NULL,
        @Device varchar(25) = NULL
    ) 
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT *
        FROM MyTable
        WHERE 
          Date < @EndDate 
          AND Date > @StartDate 
          AND ( @Location IS NULL OR Location = @Location)
          AND ( @Device IS NULL OR Device = @Device )

    )

This would work if you're using a UD table type for the location, as you asked about in your comment. Just make sure the input parameter is Read Only. However, you cannot pass NULL into a table type parameter, you have to pass in an empty parameter object of that type :

CREATE TYPE LocationsType AS TABLE 
( LocationName VARCHAR(25));

CREATE TABLE MyTable (Date datetime, Location varchar(25), Device varchar(25));

CREATE FUNCTION [dbo].[DateRange] 
    (
        @StartDate date,
        @EndDate date,
        @Location LocationsType READONLY,
        @Device varchar(25) = NULL
    ) 
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT *
        FROM MyTable
        WHERE 
          Date < @EndDate 
          AND Date > @StartDate 
          AND ( ((select count(*) FROM @Location) = 0) OR Location IN (select LocationName from @Location))
          AND ( @Device IS NULL OR Device = @Device )

    )

Call it:

declare @l LocationsType

select *
from [dbo].[DateRange] (getDate(), GetDate(), @l,NULL)

Best Answer

Related Solutions

Sql-server – Stuff function in T-SQL

Sql-server – Conditional WHERE Clause in a Table Valued Function

Related Question