user3020047 February 2016

Extracting a portion of a value out of a database column using SQL server

I'm trying to extract a portion of a value out of a database column using SQL server. The example below works in a simple context with a varchar field. The result is: &kickstart& which is what I want.

I now want to do the same when retrieving a column from the database. But SQL does not like what I am doing. I'm thinking it is something easy that I am not seeing.

Declare @FileName varchar(20) = '&kickstart&.cfg'
Declare @StartPos integer = 0
Declare @FileNameNoExt varchar(20)

SELECT @FileNameNoExt = Left(@FileName,( (charindex('.', @FileName,  0)) - 1))
SELECT @FileNameNoExt    

Here is the SQL statement that I can't seem to get to work for me:

Declare @FileNameNoExt as varchar(20)

SELECT 
    i.InstallFileType                                  AS InstallFileType,
    o.OSlabel                                          AS OSLabel,
    SELECT @FileNameNoExt = (LEFT(oi.FIleName,( (charindex('.', oi.FIleName,  0) ) - 1) )) AS FileNameNoExt,
    oi.FIleName                                        AS FIleName
FROM
    dbo.OperatingSystemInstallFiles oi 
    JOIN dbo.InstallFileTypes i ON oi.InstallFileTypeId = i.InstallFileTypeId
    JOIN dbo.OperatingSystems o ON oi.OperatingSystemId = o.OperatingSystemId 

Answers


Bacon Bits February 2016

Why do you need the variable at all? What's wrong with:

SELECT 
    i.InstallFileType                                  AS InstallFileType,
    o.OSlabel                                          AS OSLabel,
    LEFT(oi.FIleName,( (charindex('.', oi.FIleName,  0) ) - 1) ) AS FileNameNoExt,
    oi.FIleName                                        AS FIleName
FROM
    dbo.OperatingSystemInstallFiles oi 
    JOIN dbo.InstallFileTypes i ON oi.InstallFileTypeId = i.InstallFileTypeId
    JOIN dbo.OperatingSystems o ON oi.OperatingSystemId = o.OperatingSystemId


DJB February 2016

You've put a SELECT inside another SELECT list without nesting, which is a syntax error in SQL Server.

You are also attempting to assign a variable while performing a data-retrieval operation. You can select all data to be shown, or all data into variables but not both at the same time.

When the two issues above are resolved, I think you may still run into issues when committing filenames into a variable which only allows 20 characters - but then I don't know anything about your dataset.

Post Status

Asked in February 2016
Viewed 1,858 times
Voted 7
Answered 2 times

Search




Leave an answer