SQL query concatenation to create file path

Question

I need to create a file path from 3 columns in a SQL query. This will be utilized in a file once everything is completed. I have tried using CONCAT and string methods for the columns but no luck. The query is provided below.

SELECT 
    dbo.TBIndexData.DocGroup, 
    dbo.TBIndexData.Index1 AS Title, 
    dbo.TBIndexData.Index2 AS Meeting_Date, 
    dbo.TBIndexData.Index3 AS Meeting_Number,
    dbo.TBIndexData.Index4 AS Meeting_Type, 
    dbo.TBIndexData.Index5 AS Doc_Name, 
    dbo.TBIndexData.Index6 AS Doc_Type,
    dbo.TBIndexData.Index7 AS Meeting_Page, 
    dbo.TBIndexData.Index8 AS Notes, 
    dbo.TBIndexData.Index9 AS TBUser, 
    dbo.TBIndexData.Index10 AS Date_Scanned, 
    CONCAT (dbo.TBPrimary.FileDir + '\' + dbo.TBPrimary.TimsFileID + '.' + dbo.TBPrimary.FileExtension) AS FilePath
FROM 
    dbo.TBIndexData
JOIN 
    dbo.TBPrimary ON dbo.TBIndexData.DocGroup = dbo.TBPrimary.DocGroup

CONCAT is SQL Server 2012. And doesn't have that syntax. What version are you actually on? 2008 per the tag? — Martin Smith
– Martin Smith, Commented Feb 17, 2014 at 14:18
dbo.TBPrimary.FileDir + '\' + dbo.TBPrimary.TimsFileID + '.' + dbo.TBPrimary.FileExtension AS FilePath — Ian Kenney
– Ian Kenney, Commented Feb 17, 2014 at 14:21
@IanKenney - Though if TimsFileID is numeric you need a cast. — Martin Smith
– Martin Smith, Commented Feb 17, 2014 at 14:26
@MartinSmith I am using SQL 2008 as per the tag. Why is CONCAT not available? So use CAST, not CONCAT since the column TimsFileID is always numeric? — clerktech
– clerktech, Commented Feb 17, 2014 at 14:38
Because it isn't implemented as a recognized function until SQL Server 2012. You need to use + and CAST any non string components yourself. — Martin Smith
– Martin Smith, Commented Feb 17, 2014 at 14:39

Martin Smith · Accepted Answer · 2014-02-17 14:49:38Z

2

In SQL Server 2008 you need something like

SELECT I.DocGroup,
       I.Index1  AS Title,
       I.Index2  AS Meeting_Date,
       I.Index3  AS Meeting_Number,
       I.Index4  AS Meeting_Type,
       I.Index5  AS Doc_Name,
       I.Index6  AS Doc_Type,
       I.Index7  AS Meeting_Page,
       I.Index8  AS Notes,
       I.Index9  AS TBUser,
       I.Index10 AS Date_Scanned, 
       P.FileDir + '\' + CAST(P.TimsFileID AS VARCHAR(10)) + 
            '.' + P.FileExtension AS FilePath
FROM   dbo.TBIndexData I
       JOIN dbo.TBPrimary P
         ON I.DocGroup = P.DocGroup

You shouldn't use schemaname.tablename in the SELECT list. This is not officially supported grammar. Just use tablename or give an alias.

(Using two part names there can lead to confusing errors if calling properties of columns of CLR datatypes)

edited Feb 17, 2014 at 14:49

answered Feb 17, 2014 at 14:43

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

clerktech Over a year ago

that makes a lot of sense. I should of cleaned that up before posting because I was in AQT not using a defined table but running overall queries. Going to put myself in programmer's timeout.

Hayk Mantashyan · Accepted Answer · 2014-02-17 14:23:50Z

-1

Try to use CONCAT with commas

CONCAT (dbo.TBPrimary.FileDir, '\', dbo.TBPrimary.TimsFileID, '.', bo.TBPrimary.FileExtension) AS FilePath

answered Feb 17, 2014 at 14:23

Hayk Mantashyan

3282 silver badges18 bronze badges

2 Comments

marc_s Over a year ago

No - CONCAT is not available in the 2008 version of SQL Server - it's a new feature in SQL Server 2012 and newer

Hayk Mantashyan Over a year ago

Maybe this link will be helpfull? codeproject.com/Questions/310086/…

Collectives™ on Stack Overflow

SQL query concatenation to create file path

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related