March 4, 2014 at 1:04 pm
curious_sqldba (3/4/2014)
Thank you taking time in replying.
So if you copy paste the ouput in excel you will see that just first two words are together and all the other one's still have tab delimiters and they show up in different lines.
No there are no TABS left if you run that code.
The new lines are not TABS that are carriage returns (char(13)).
SELECT SomeValue AS theData ,
REPLACE(REPLACE(SomeValue, ' ', ''), CHAR(9), '') AS dataNoSpaces,
charindex(char(9), REPLACE(REPLACE(SomeValue, ' ', ''), CHAR(9), '')) AS FirstLocationOfTab,
charindex(char(13), REPLACE(REPLACE(SomeValue, ' ', ''), CHAR(9), '')) AS FirstLocationOfCarriageReturn
from #Something
_______________________________________________________________
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/
March 4, 2014 at 1:14 pm
As Sean has said, they aren't tabs, they are carriage returns (CHAR(13)) If they were tabs you wouldn't get multiple rows, you'd get multiple columns in excel.
There are many different white space characters and when you aren't getting the results you need you need to find out what character is really there. You can use a numbers/tally table to break out a string into each character and use ASCII([character]) to find out the ASCII code for each character and then replace the codes you get for white space. I use http://www.asciitable.com/ when I'm wondering what an ASCII code stands for.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply