|
Hi Everyone! I have a query I'm running to remove white spaces because TRIM was not working. The query is: FROM Items This removes the white spaces and aligns the items in the CSV format with an exception of one item. If I run the query in SSMS and copy and paste the data results the spaces are no longer there. But during export the item is splitting into 3 columns, though in SSMS it is one column. Can someone assist me in finding a way to remove trailing spaces with what I have or maybe I need to try a different way? (Minus using TRIM because those options did not work.) Any help is greatly appreciated!
(comments are locked)
|
|
It's because the text itself contains commas. So when you export it in comma separated file, then the application which imports the data will handle it as 3 separate columns. You will have to use another separator (eg. semicolon or tab). For Tab delimited file you have the option directly in SSMS. When exporting select the tab delimited format. Other option would be text qualify the strings by putting them into double quotation marks if the importing application can handle this. You can achieve this easily by the OMG...I totally didn't even think about the comma delimited part. UGH! Let me look at changing the separator. I will follow up shortly.
Jul 11 '12 at 08:52 PM
Noonies
Thanks again Pavel! Saved the day for me. I ended up using the QUOTENAME and that worked perfect.
Jul 12 '12 at 08:47 PM
Noonies
@Noonies I guess you owe accepting Pavel's answer then ;) But I must also add that QUOTENAME can handle only 128 characters and your FullDescription can hold 200. So whenever the characters would be greater than 128, the data returned would be NULL. Also, your white space removal process is horrible and it can actually truncate the data after the last whitespace. For instance in your example 'Jon Riverst/Bost/Black Tied Dyeds, YellowOra, Colored Tankts 10.8 Med' is truncated to 'Jon Riverst/Bost/Black Tied Dyeds, YellowOra, Colored Tankts 10.8'. Consider using LTRIM/RTRIM/REPLACE whatever fulfill your needs.
Jul 13 '12 at 06:28 AM
Usman Butt
@Usman Butt - Thanks for your feedback! I had tried LTRIM/RTRIM/REPLACE prior to posting and these were not working for me so I was not sure where to go from there.
Jul 13 '12 at 03:21 PM
Noonies
@Noonies You could have shared that problem with us. May be we could have help. But I am happy that you accepted the answer unlike some other users. This encourages the volunteers more and also help maintaining the site.
Jul 13 '12 at 03:25 PM
Usman Butt
(comments are locked)
|
can you post sample data and the field definition?
CREATE TABLE [dbo].[Items]( [RecKey] [nvarchar](15) NOT NULL, [FullDescription] [nvarchar](200) NULL, [PrimarySupNid] [int] NULL )
INSERT INTO [dbo].[Items] ([RecKey] ,[FullDescription] ,[PrimarySupNid]) VALUES ('11135' ,'Jon Riverst/Bost/Black Tied Dyeds, YellowOra, Colored Tankts 10.8 Med' ,790)
GO
This is the item causing me problems. Where the Med is, we have the same item with Lar and Sma which also has the same issue.
Please let me know if you need anything additional. Thanks for responding so quickly.