Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
george.greiner
Starting Member
19 Posts |
 Posted - 2009-11-06 : 14:32:21
|
| Hello,I have an issue in my SQL server that has to do with Chr(13) and Chr(10). While viewing this table in access it shows up in 3 lines as obvious character return and line feeds but when viewing or trying to run a query in TSQL it shows up as U+25A1 which is a white box for each of those characters. Also funny enough if I copy and paste it into just about anything it functions correctly. Because of this I cannot run my queries on this data to split that field into 4 fields and later query that data and send it to my client in that way (they like most people in the world use more than 1 field for an address and name lol silly city).Any help would be much appreciated.BTW the code I am using to process this is as follows:declare @Delimiter char(13);select @Delimiter = '';;with Numbers (Number) as (select row_number() over (order by object_id) from sys.columns),Split (Sub, Seq, ID) as (select replace(Split, char(13), ''), Seq, recID from copyofdefendantsAll cross apply (select substring(defendant_info + @Delimiter, Number, charindex(@Delimiter, defendant_info + @Delimiter, Number) - Number) Split, row_number() over (order by Number) as Seq from Numbers where Number <= len(defendant_info) and substring(@Delimiter + defendant_info, Number, 1) = @Delimiter) as Splitter)select ID, [1] as PlaceName, [2] as Street, left([3], len([3])-1) as CitySt,right([3], 5) as Zipfrom(select Sub, Seq, IDfrom Split) as Prepivotpivot(max(Sub) for Seq in ([1], [2], [3])) as Pvt;The results of that in the current state are :Example field: (note that the Chr(13) and Chr(10) are properly executed)RIGDON MILLER & CO REAL ESTATE1936 SPRUCE ST19103ID| 10330188PlaceName| RIGDON MILLER & CO REAL ESTATE 1936 SPRUCE ST 19103Street| IGDON MILLER & CO REAL ESTATE 1936 SPRUCE ST 19103CitySt| GDON MILLER & CO REAL ESTATE 1936 SPRUCE ST 1910Zip| 19103 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-11-06 : 19:50:30
|
| What environment are you running your T-SQL code in? If it is Enterprise Manager (Query Analyzer), try changing the output format to Text instead of Grid.=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
george.greiner
Starting Member
19 Posts |
Posted - 2009-11-09 : 08:21:41
|
quote:
Originally posted by Bustaz Kool
What environment are you running your T-SQL code in? If it is Enterprise Manager (Query Analyzer), try changing the output format to Text instead of Grid.
I am running my T-SQL code in SQL server management studio and changing it to text makes the code not function at all. |
|
|
|
george.greiner
Starting Member
19 Posts |
Posted - 2009-11-09 : 08:28:40
|
quote:
Originally posted by X002548
my how CTE usage has exploded....And I suspect without a very good understand of why or howIn any casepost sample data in DML format and your expected results for that data pleaseBrett8-)
I do not know what DML format is. The data is pretty simple as the field looks as such:Smith, Anthony1234 Market StreetPhiladelphia, PA 19104In the SQL table it looks like (U+25A1) = the white square but for some reason in here it comes up as a smiley face.Smith, Anthony(U+25A1)(U+25A1)1234 Market Street(U+25A1)(U+25A1)Philadelphia, PA 19104I need this data to look like:Name Street ZipSmith, Anthony 1234 Market Street 19104I do not care about the city state as I have a zip code table I can compare to as this data will much of the time not have city and state listed.To your "my how CTE usage has exploded...." what do you mean by that and is this the wrong way to about this? I attempted to do this using the method I use in Access but I was pushed to do it in this way by someone else that said it would function better. |
|
|
|
|
|
|
|
|
Data Issue