CODEWITHSUNDEEP
sql
MoiD101
MoiD101

Reputation: 195

prevent leading zeros being stripped when outputting to csv from sql server

Hi i have a SQL script which outputs some columns of data one of which is a phone number, into a csv file. The script all works fine however the leading zero is stripped from the phone number. Now i know all about why leading zeros are stripped from phone numbers when opening a csv in excel that contains them.

The bit im struggling with is the SQL script to surround the phone number in double quotes so that its interpreted as text when opened. see my sql below:

SET @query ='
SELECT
    ''MobilePhoneNumber'' AS MobilePhoneNumber
    ,''RequestNumber'' AS RequestNumber
    ,''RequestDate'' AS RequestDate
    ,''RequestType'' AS RequestType
    ,''Status'' AS Status
UNION ALL
SELECT 
    [MobilePhoneNumber] --I Want to get this column in the csv as EG "01234123456"
    ,[RequestNumber]
    ,CONVERT(VARCHAR,[RequestDate],120)
    ,[RequestType]
    ,[Status]
FROM [Mobiles].[dbo].[Requests]
WHERE [RequestType] = ''Cease''
    AND DATEDIFF(mm,Requestdate , GETDATE())=1;'

Upvotes: 1

Views: 7149

Answers (2)

MoiD101
MoiD101

Reputation: 195

Thanks to the suggestion from @KekuSemau Here is my solution in the end for the benefit of others:

SET @query ='
SELECT
    ''MobilePhoneNumber'' AS MobilePhoneNumber
    ,''RequestNumber'' AS RequestNumber
    ,''RequestDate'' AS RequestDate
    ,''RequestType'' AS RequestType
    ,''Status'' AS Status
UNION ALL
SELECT 
    CHAR(39) +[MobilePhoneNumber] -- < I simply added the CHAR(39)
    ,[RequestNumber]
    ,CONVERT(VARCHAR,[RequestDate],120)
    ,[RequestType]
    ,[Status]
FROM [Mobiles].[dbo].[Requests]
WHERE [RequestType] = ''Cease''
    AND DATEDIFF(mm,Requestdate , GETDATE())=1;'

Upvotes: 0

KekuSemau
KekuSemau

Reputation: 6856

Try this one:

'...
SELECT 
CHAR(34) + [MobilePhoneNumber] + CHAR(34)
...'

Or 

'...
SELECT 
''"'' + [MobilePhoneNumber] + ''"''
...'

Upvotes: 1

Related Questions