Conditional extraction of fixed width data using SQL

Question

I have a scenario where I pull out data from multiple tables and the output is fixed width format. The fixed width output will look like:

Current output:

1001RJOHNKEITH25 20181017 NA
1002CDWANEKANE36 20181010 RR
1003CMIKAYLAGN44 20181011 RR

Desired output:

1001RJOHNKEITH25 20181017 NA
1002CDWANEKANE36          NA
1003RMIKAYLAGN44 20181010 RR

In this output, 1001 is the Person ID, R/C is the hard-coded indicator, then comes the name, age and registration date, record type. There is a condition for Registration date. If the record indicator is R, the registration date will show up. Otherwise, it should be null. I am not sure how to write a condition based on the fixed width field.

Rextester demo attached : https://rextester.com/MKESI50760

Any help?!

Ryan B. · Answer 1 · 2018-10-17T15:34:42.787

1

You just need to update one line in your query:

LEFT(CONCAT(isnull(t.registrationdate,''),space(14)),14), -- Registration date should show up when record indicator is 'R'

becomes

LEFT(CONCAT(isnull(CASE WHEN t.registeredonline = '1' and t.recordtype = 'NA' THEN CONVERT(char(10), t.registrationdate,126) ELSE NULL END,''),space(14)),14), -- Registration date should show up when record indicator is 'R'

This will check your date field and put in spaces instead of a date when the logic for record indicator evaluates to'R'

The 'convert' statement is needed otherwise the NULL date will end up showing as 1900-01-01.

Hope it helps.

edited Oct 17 '18 at 15:34

answered Oct 17 '18 at 14:52

Ryan B.

3,575
2
20
26

Is there a way to use the 'Indicator' field directly without using the 'Record type' field from the table? Because in my actual code the logic for Indicator is huge and complex. So Is there a way to extract Indicator from fixed width output and use in conditioning? – Rick Oct 17 '18 at 14:59
1

I posted a new answer to address this. – Ryan B. Oct 17 '18 at 15:38

Sorin G · Answer 2 · 2018-10-17T15:03:42.137

1

The line

LEFT(CONCAT(isnull(t.registrationdate,''),space(14)),14)

become

CASE WHEN t.registeredonline = '1' and t.recordtype = 'NA' THEN LEFT(CONCAT(isnull(t.registrationdate,''),space(14)),14) ELSE SPACE(14) END, --     Registration date should show up when record indicator is 'R'

Just enclosing the original line with a condition to see if the result is 'R' or not. The condition is showed up in the query from your link.

edited Oct 17 '18 at 15:03

answered Oct 17 '18 at 15:02

Sorin G

11
3

Is there a way to use the 'Indicator' field directly without using the 'Record type' field from the table? Because in my actual code the logic for Indicator is huge and complex. So Is there a way to extract Indicator from fixed width output and use in conditioning? – Rick Oct 17 '18 at 15:03

score 1 · Accepted Answer · answered Oct 17 '18 at 15:33

OK, well this is a little messy. But because your output is fixed width, you can always make the query into a view or a CTE (shown below) and then access specific positions in the string via SUBSTRING function.

There are LOT of drawbacks to doing this. If anybody changes the order or size of the fields being concatenated ... it all breaks. So, in the spirit of answering your question.. this is a way to do it. But I don't think It's a good way.

WITH BaseQuery as

(
    select 
    t.Cid,
    cast
    (
     concat(
         LEFT(CONCAT(isnull(t.Cid,''),space(5)),5), -- PersonID
         LEFT(CONCAT(isnull
              ((case when t.registeredonline = '1' and t.recordtype = 'NA' then 'R'
                    else 'C' end),''),space(10)),10),-- Record Indicator
         LEFT(CONCAT(isnull(t.name,''),space(14)),14), --name
         LEFT(CONCAT(isnull(t.age,''),space(5)),5), --age
         LEFT(CONCAT(isnull(t.registrationdate,''),space(14)),14), -- Registration date should show up when record indicator is 'R'
         LEFT(CONCAT(isnull(t.recordtype,''),space(3)),3) --Record type  
         ) as nvarchar(max) 
        ) result
     from #temp t
)

SELECT
    CONCAT(
        SUBSTRING(result, 1, 34) -- portion before the 'registration date' region
        , CASE WHEN SUBSTRING (RESULT, 6, 1) = 'R' THEN SUBSTRING (RESULT, 35, 10) ELSE SPACE(10) END 
        , SUBSTRING (RESULT, 46, 5)
        )

FROM 
    BaseQuery

this gives the result:

1001 R         JOHNKEITH     25   2018-10-17   NA
1002 C         DWANEKANE     36                RR
1003 C         JOHNKEITH     44                RR

Thanks, this is my test data. my actual data has fixed width for each field and this helped !! — Rick, Oct 17 '18 at 16:20

score 1 · Answer 4 · answered Oct 17 '18 at 15:36

Dealing with fixed width data:

Data in a fixed-width text file or string is arranged in rows and columns, with one entry per row. Each column has a fixed width, specified in characters, which determines the maximum amount of data it can contain. No delimiters are used to separate the fields in the file.

Parsing that data in T-SQL you can use SUBSTRING

https://learn.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-2017

SUBSTRING ( expression ,start , length )

Here's an example:

DECLARE @SampleData TABLE
    (
        [LineData] NVARCHAR(255)
    );

INSERT INTO @SampleData (
                            [LineData]
                        )
VALUES ( '1001RJOHNKEITH25 20181017 NA' )
     , ( '1002CDWANEKANE36 20181010 RR' )
     , ( '1003CMIKAYLAGN44 20181011 RR' );

SELECT SUBSTRING([LineData], 1, 4) AS [PersonId]
     , SUBSTRING([LineData], 5, 1) AS [Indicator]
     , SUBSTRING([LineData], 6, 9) AS [Name]
     , SUBSTRING([LineData], 15, 2) AS [Age]
     , SUBSTRING([LineData], 18, 8) AS [RegDate]
     , SUBSTRING([LineData], 27, 2) AS [RecordType]
     , *
FROM   @SampleData;

So in your example you're wanted to evaluate whether or not the "Indicator" is 'R', you can get to that value with:

SUBSTRING([LineData], 5, 1)

Not sure how that fits into what you have been tasked with. Based on other comments there's more to how this "Indicator" is determined.

Not ideal, but you could parse out all the fields and then put them back together doing the evaluation on that indicator field or use stuff in a case statement to replace the date with blanks when evaluating if indicator is R in the string.

DECLARE @SampleData TABLE
    (
        [LineData] NVARCHAR(255)
    );

INSERT INTO @SampleData (
                            [LineData]
                        )
VALUES ( '1001RJOHNKEITH25 20181017 NA' )
     , ( '1002CDWANEKANE36 20181010 RR' )
     , ( '1003CMIKAYLAGN44 20181011 RR' );

--We check for R using substring
--when not equal to R we replace where Registration date in the string was with blanks.
SELECT CASE WHEN SUBSTRING([LineData], 5, 1) = 'R' THEN [LineData]
            ELSE STUFF([LineData], 18, 8, '        ')
       END AS [LineData]
FROM   @SampleData;

score -1 · Answer 5 · answered Oct 17 '18 at 14:40

-1

Select ColA, CASE WHEN ColB (Criteria here) THEN NULL ELSE ColB END AS ColB, ColC

answered Oct 17 '18 at 14:40

Tamara Pattinson

87
4
10

Conditional extraction of fixed width data using SQL

5 Answers5