July 9, 2015 at 5:48 am
hi
i am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs! any ideas for me?
thanks
mal
select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME,
CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN
,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I
LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID
LEFT JOIN INVENTITEMGTIN IT ON I.ITEMID= IT.ITEMID
WHERE
--I.ITEMID = '730086' and
IG.ITEMGROUPID IN ('Indoor','Outdoor')
AND I.[PARTITION] = '5637144576'
results:
Gießanzeiger groß ,04260376730475 04260376730475 730475 00
Gießanzeiger klein , 04260376730352 04260376730352 730468 00
Gießanzeiger klein , 04260376730468 04260376730468 730468 00
July 9, 2015 at 7:16 am
dopydb (7/9/2015)
hii am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs! any ideas for me?
thanks
mal
select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME,
CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN
,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I
LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID
LEFT JOIN INVENTITEMGTIN IT ON I.ITEMID= IT.ITEMID
WHERE
--I.ITEMID = '730086' and
IG.ITEMGROUPID IN ('Indoor','Outdoor')
AND I.[PARTITION] = '5637144576'
results:
Gießanzeiger groß ,04260376730475 04260376730475 730475 00
Gießanzeiger klein , 04260376730352 04260376730352 730468 00
Gießanzeiger klein , 04260376730468 04260376730468 730468 00
Are you sure you are getting tabs? You have cast your columns as char(40) so you are going to get spaces. The first column you posted as your results is exactly 40 characters in length. If you don't want the trailing spaces then you should use varchar instead of char.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 9, 2015 at 7:18 am
You're casting your strings as CHAR(), so it's padding your strings with spaces as necessary at the end. You want to cast it to VARCHAR().
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 9, 2015 at 7:28 am
I want the leading space hence the char (40) etc
but yea definitely getting a tab -when i post my data its removing the formatting on the post
July 9, 2015 at 7:34 am
dopydb (7/9/2015)
I want the leading space hence the char (40) etcbut yea definitely getting a tab -when i post my data its removing the formatting on the post
How do you know it is getting tabs? I don't see how that is possible from the code you posted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 9, 2015 at 7:39 am
hi sean
did you view the link above?
definitely is a tab to me - i can count the 40 Chars including spaces then the space after the , is one count?
mal
July 9, 2015 at 8:04 am
dopydb (7/9/2015)
hi seandid you view the link above?
definitely is a tab to me - i can count the 40 Chars including spaces then the space after the , is one count?
mal
It is a picture. You can visually tell the difference between spaces and a tab in a picture. That doesn't look like a tab to me. It looks like fixed width columns, which is exactly what you have when you specify char(40). Have you tried changing to varchar(40)?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 9, 2015 at 8:05 am
How are you getting the results from SQL into Notepad?
If you are doing a copy/paste from SSMS, and your SSMS results are set to go to a grid, then there will be a tab between the columns.
Just for trying this out, please run this code and see if you still have the tabs (instead of separate columns, all the data is being put into one column). Please let us know if you still have tabs. (Notice that I even took out the replace of char(9)...)
SELECT CAST(NAMEALIAS AS CHAR(40)) + ',' +
CAST(ISNULL(GLOBALTRADEITEMNUMBER,0) AS CHAR(18)) +
LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) AS CHAR(18))) +
LTRIM(CAST(I.ITEMID AS CHAR(8))) +
'00'
FROM INVENTTABLE I
LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID
LEFT JOIN INVENTITEMGTIN IT ON I.ITEMID = IT.ITEMID
WHERE
--I.ITEMID = '730086' and
IG.ITEMGROUPID IN ('Indoor','Outdoor')
AND I.[PARTITION] = '5637144576'
If your tabs are gone, go back to your original query (with the separate columns), and set SSMS to "Results to Text". Run the query, and see if you have the tabs there.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 9, 2015 at 8:08 am
that was a little stupid! 😀
uploaded file now, set as varchar, but still getting a tab space
July 9, 2015 at 8:12 am
think thats it wayne!
no more tabs
THANKS FOR HELP FOLKS
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply