How to stop a query turning text into a number

Other Tech Topics
1

I am pulling data from an SQL db into excel using vba.
One of the fields I need is a string like '051','052'
The SQL string I used was
StrSql = "SELECT afgart__.afg__ref, afgart__.prkl_ref, afgart__.etiket_b, afgart__.etiket_h, afgart__.off1_ref "
StrSql = StrSql & "FROM sqlb00.dbo.afgart__ afgart__"
The field causing the problem is prkl_ref
The result of the query using this string ism a number 50 or 51 etc It is Placed on the right side of the excel cell.
I bit of research gave me CAST
as CAST(afg__art.prkl_ref as VARCHAR(1012)) This gave me a number again
More research gave me CONCAT
As CONCAT( '0', afg__art.prkl_ref) that gave me "0050" "0051" etc a step forward. But this has stopped working so I will have changed something.
I also tried CONCAT "'" , afg__art.prkl_ref but this caused havoc as the ' was treated as a REM comma thus loosing the rest of the code.
I am sure that there is a way to force the return of a string but I cant find it and would welcome some help
John

2

The query is actually returning a string, it's just that Excel will trim leading zeros from values to make them numbers. If embedding a single quote ' character doesn't work, you'll need to assign a Text format to those columns before importing the data.

3

Robert
Many thanks for your interest in my problem. I had just about decided that it was an Excel problem not SQL. I had tried attaching a ‘ but couldn’t get the syntax right so I did the transformation in Excel. It would be neater done in the query though. Could you help with the SQL to enable the download of ‘051 so Excel can’t b***er it up?
Thanks in advance.
John

4

Something like:

SELECT '''' + CAST(afg__art.prkl_ref as VARCHAR(20))

or

SELECT quotename(CAST(afg__art.prkl_ref as VARCHAR(20)),'''')

5

Or:

SELECT CONCAT(char(39), afg__art.prkl_ref)

Assuming the column is already a string with the leading zeroes.