CODEWITHSUNDEEP
sql
john french
john french

Reputation: 379

remove trailing commas using sql

I am doing some data mapping and out of my 15 columns on 5 will have data the rest will be empty. When I export my fie as csv my columns that have no data return comas. My vendor needs those trailing comas removed. I tried using trim on one of the colmns and that did not work. sample

sample :

Clientid,schoolc,schname,District,state,NCESid,PartnerID,Action,Reserved1,Reserved2,Reserved3,Reserved4,Reserved5,Reserved6,Reserved7,Reserved8,Reserved9,reserved10 ca-rosev42441,26,barns Elementary,Sample City School District,CA,163364418255,,,,,,,,,,,,

Upvotes: 0

Views: 3182

Answers (5)

DaveN
DaveN

Reputation: 918

The following works for data with spaces provided the broken-bar character is not in your data (my favorite dummy character, but totally arbitrary)

CREATE FUNCTION [dbo].[RTRIMCHAR] (@Input NVARCHAR(max), @TrimChar CHAR(1) = ',')
RETURNS NVARCHAR(max)
AS
BEGIN

    RETURN REPLACE(REPLACE(RTRIM(REPLACE(REPLACE(@Input,' ','¦'), @TrimChar, ' ')), ' ', @TrimChar),'¦',' ')
END


GO

And, for where this came from:

CREATE FUNCTION [dbo].[LTRIMCHAR] (@Input NVARCHAR(max), @TrimChar CHAR(1) = ',')
RETURNS NVARCHAR(max)
AS
BEGIN
      RETURN REPLACE(REPLACE(LTRIM(REPLACE(REPLACE(@Input,' ','¦'), @TrimChar, ' ')), ' ', @TrimChar),'¦',' ')
END



GO

Upvotes: 1

Jeremy S
Jeremy S

Reputation: 1043

The easiest way would be to write a query where the SELECT clause contains only the columns your vendor wishes to see, and export the results of that query as a CSV file.

Upvotes: -1

Leonel Folmer
Leonel Folmer

Reputation: 211

See this example:

CREATE FUNCTION [dbo].[ufn_TrimLeadingCharacters_reversed] ( @Input VARCHAR(50), @LeadingCharacter CHAR(1) )
RETURNS VARCHAR(50)
AS
BEGIN
    RETURN reverse(REPLACE(LTRIM(REPLACE(reverse(@Input), ISNULL(@LeadingCharacter, '0'), ' ')), 
                   ' ', ISNULL(@LeadingCharacter, '0')))
END



select dbo.ufn_TrimLeadingCharacters_reversed('jazz,guitar,bass,strings,,,,,',',')

Upvotes: 1

radar
radar

Reputation: 13425

Are u inserting data using bulk insert ? You can define a format file so that only first 5 columns are read.

Instead If you already have the entire string, you can search for ,, using charindex and use substr.

Upvotes: 0

Branden Schwartz
Branden Schwartz

Reputation: 181

There is very likely a much better approach to get the formatting you're after, but assuming that
-the first five columns will have some data with length >=1
-two commas together indicates the beginning of the end (of the line at least)
you can handle each line/row of data individually in a manner similar to the LEFT(...) call below

Something like the example snippet below should work. Note that the DECLARE, SET, and PRINT statements are only required to set up the example and the LEFT(...) is all you need if @line contains the row of data you want to output. If we can see how the data is being pulled out of the table(s) there may be a much more sensible and efficient way to drop the extraneous commas.

DECLARE @line nvarchar(max)
SET @line = 'Clientid,schoolc,schname,District,state,NCESid,PartnerID,Action,Reserved1,Reserved2,Reserved3,Reserved4,Reserved5,Reserved6,Reserved7,Reserved8,Reserved9,reserved10 ca-rosev42441,26,barns Elementary,Sample City School District,CA,163364418255,,,,,,,,,,,,'

SET @line = LEFT(@line,CHARINDEX(',,',@line)-1) ---NoTrailingCommas

PRINT @line

Upvotes: 0

Related Questions