How do I Query a fixed positional table

Question

I have a fixed positional table in sql server. Its a two column table. One primary key, which is not needed or useful in query and the rest of the 50 fields in one column varchar(4000). I want to select for three different data fields in the table and create a temp table to do more queries on. fields are padded with blanks so all variables are in same locations in file. Not sure where to begin and could not find much on this topic.

This is a terrible design. Why store 50 fields in one column?, can't you have 50 columns in your table?. — Lamak, May 02 '14 at 21:01
@Lamak - It might be possible that 50 fields are actually attributes which could be null for many id's. So, they probably have a max of 50 rows per id. I have seen this in the real world, i.e beyond textbooks. — Erran Morad, May 02 '14 at 21:06
You are effectively using SQL Server tables as tab-delimited files. This gives you the worst of both worlds. If you possibly can, create sensibly-typed columns for all of your subdata. — mwigdahl, May 02 '14 at 21:06
@BoratSagdiyev 50 rows per id?, this isn't what op is saying. S/he says that s/he has 50 columns stored in one. And that's a terrible design — Lamak, May 02 '14 at 21:07
I did not create the format, its a standard format used for a specific business area for data exchange of standardized files. I agree its awful, who would think it was a good idea, but a number of organizations got together and decided on it. Now I want to query data from the table.....Just difficult to figure out... — vfiola, May 02 '14 at 21:14
@vfiola -- sounds like an EDL format of some sort. I don't see why it has to go into the database as a big blob of text, though. Is it not possible to parse it before putting it into your database, and get it structured a bit more appropriately? It will make querying it much easier. — mwigdahl, May 02 '14 at 21:19
It was created by someone else from another table and locked as the final cleaned data set for a year which is ready for export. I dont have a copy outside of databvase and if I querry the source I get more data than in the table. I guess I export and re-import....Thanks! — vfiola, May 02 '14 at 21:24
@vfiola -- you may have another possibility, although it is likely to be kind of ugly. I'll submit it as an answer; you can see whether it gets you going or not (not knowing your delimiter format it almost certainly won't work without modification). — mwigdahl, May 02 '14 at 21:28

score 1 · Accepted Answer · answered May 02 '14 at 21:30

;WITH CTE AS (
    SELECT RowID
        , SUBSTRING(Bigfield, 4,6) field1 
        , SUBSTRING(Bigfield, 11,2) field2 
        , SUBSTRING(Bigfield, 15,2) field3
    FROM Table
)
SELECT C.* 
FROM TableC 
    JOIN CTE ON C.RowID= CTE.RowID
WHERE CTE.field3 like '%re%'

Ugly and I agree that exporting it and importing it into a "real" structure would be better but here's one way to do it if you needed.

score 0 · Answer 2 · edited May 23 '17 at 12:05

You're in a bad spot, but this might get you on the track to getting this data broken out into something you can work with, depending on your delimiter format. The split function is from this article, and is designed to work with single-character delimiters. Since you're using fixed-width fields, you will likely need to modify the function to get it to do what you want to do (I'm filtering out the numerous blank rows you would get with my sample data because of this).

create table ##data (id int identity, data varchar(4000))

insert into ##data (data) values ('some_text2  12  21        44     xxx')
insert into ##data (data) values ('some_text   10  20        40     xyz')

select id, split.pn, split.s
FROM ##data dt
    CROSS APPLY dbo.Split(' ', dt.data) AS split
WHERE split.s != ''



CREATE FUNCTION dbo.Split(@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )

score 0 · Answer 3 · answered May 05 '14 at 16:46

0

Okay. I found this worked greatand I did not have to export and re-import.

SELECT
SUBSTRING(rec,58,4) + '/' +

SUBSTRING(rec,62,2) + '/' +

SUBSTRING(rec,64,2) as dDate,

(SUBSTRING(rec,132,3)) as dCounty

from tablename

answered May 05 '14 at 16:46

vfiola

79
1
1
6

How do I Query a fixed positional table

3 Answers3