SQL ServerSelect

SQL Server - Select - Cross Apply

The Cross Apply is a select clause that returns 1 record. It is then an extra column that can be used for
other purposes.

1
2
3
4
5
-- cross apply test
select OrgDataTrimmed
       , f1.* 
from [OthApps173_Original] mt
cross apply ( select str = mt.OrgDataTrimmed + '***') f1

This next statement was created to read a flat file containing a directory and turning it into individual records.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- detailed cross apply example
select OrgDataTrimmed
       , ParsedData.* 
from [OthApps173_Original] mt
cross apply ( select str = mt.OrgDataTrimmed + '          ') f1
cross apply ( select p1 = charindex( ' ', str ) ) ap1
cross apply ( select p2 = charindex( ' ', str, p1 + 1 ) ) ap2
cross apply ( select p3 = charindex( ' ', str, p2 + 1 ) ) ap3
cross apply ( select p4 = charindex( ' ', str, p3 + 1 ) ) ap4
cross apply ( select p5 = charindex( ' ', str, p4 + 1 ) ) ap5
cross apply ( select p6 = charindex( ' ', str, p5 + 1 ) ) ap6
cross apply ( select p7 = charindex( ' ', str, p6 + 1 ) ) ap7
cross apply ( select p8 = charindex( ' ', str, p7 + 1 ) ) ap8
cross apply ( select p9 = charindex( ' ', str, p8 + 1 ) ) ap9
cross apply ( select p10 = charindex( ' ', str, p9 + 1 ) ) ap10
cross apply ( select p11 = charindex( ' ', str, p10 + 1 ) ) ap11
cross apply ( select p12 = charindex( ' ', str, p11 + 1 ) ) ap12
cross apply ( select p13 = charindex( ' ', str, p12 + 1 ) ) ap13
cross apply ( select p14 = charindex( ' ', str, p13 + 1 ) ) ap14
cross apply ( select p15 = charindex( ' ', str, p14 + 1 ) ) ap15
cross apply ( select p16 = charindex( ' ', str, p15 + 1 ) ) ap16
--cross apply ( select p17 = charindex( ' ', str, p16 + 1 ) ) ap17

cross apply ( select FileSize = substring( str, 1, p1-1 )                   
                 , CreateDt = substring( str, p1+1, p2-p1-1 )
                           , CreateTm = substring( str, p2+1, p3-p2-1 )
                           , CreateAmPm = substring( str, p3+1, p4-p3-1 )
                           , theFileName = substring( str, p4+1, p5-p4-1 )
                           , FileSizeAprox = substring( str, p5+1, p6-p5-1 )
                           , FileSizeUnit = substring( str, p6+1, p7-p6-1 )
                           , FileType = substring( str, p7+1, p8-p7-1 )
                           , FileType2 = substring( str, p8+1, p9-p8-1 )
                           , FileType3 = substring( str, p9+1, p10-p9-1 )
                           , FileType4 = substring( str, p10+1, p11-p10-1 )
                           , UploadedDt = substring( str, p11+1, p12-p11-1 )
                           , UploadedTm = substring( str, p12+1, p13-p12-1 )
                           , UploadedAmPm = substring( str, p13+1, p14-p13-1 )
                           , attrib = substring( str, p14+1, p15-p14-1 )
                           , dir = substring( str, p15+1, p16-p15-1 )
--                         , ft9 = substring( str, p16+1, p17-p16-1 )

          ) ParsedData
-- 65660
where charindex('pdf', OrgDataTrimmed) = 0 -- 65499
and ParsedData.attrib in ('-a-----', 'ra-----') -- 18196