March 10, 2010 at 9:58 am
I dont' know what the problem is. I created a table and set of data for you guys and i retested the problem and with the test data, there IS NO PROBLEM the code works as expected. So there must be a column that I'm not using at all that is influencing the results. I'll expand my column list to see if that helps.
Dude! I can't get help if i can't reproduce the problem!!!! HECK!
I can't think of anything else to do to the data outside of TRIM functions.
March 10, 2010 at 10:21 am
there are no triggers, no constraints or anything on this table. I've expanded the column list and I've tried to do TRIM functions on the information and i get teh same results.
I can't reproduce the problem. The test data i was going to send you works fine.
March 10, 2010 at 10:23 am
I'd suggest posting the code you've written, even though it works and carefully explaining the problem you have with the real code. It at least shows that you've put some effort into it 🙂
If you're allowed to, at least post the non-working code (even if you can't post the data).
Taking a very blind stab in the dark, ROW_NUMBER() is arbitrary when your ORDER BY clause contain ties within a partition - e.g. if you apply row_number to employees partitioned by division ordered by total sales and two employees in a division have the same total sales, then it's entirely down to the order the optimiser happens to arrive at rows that determines which will have the lower row number and this can change on subsequent runs based on a lot of factors.
March 10, 2010 at 10:39 am
I don't care about the order. Only that it count the items the are alike. For some reason, it gets to certain records that are alike and start OVER at 1 instead of continuing to increment. (What it SHOULD Do is count all skills that are exactly alike within the same level and sublevel). The original table has 3000 records this is the only category I'm having problems with.
I've attached a screen shot of the resultset in error.
This is the query I'm trying to get to work:
SELECT [Rec No], Category, [Level], Sublevel, RTRIM(LTRIM(SUBSTRING(Skill,1,90))) AS Skill,
ROW_NUMBER() OVER(PARTITION BY Category, [Level], Sublevel, RTRIM(LTRIM(SUBSTRING(Skill,1,90))) ORDER BY Category, [Level], Sublevel, RTRIM(LTRIM(SUBSTRING(Skill,1,90))))
FROM #mytable
where category = 'comp'
The only difference between it and the original is the table name: GroveStandards.dbo.GWD_MS.
Here are the test table and data (it should run easily):
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable (
[Rec No] NVARCHAR(50) NULL,
CNUM NVARCHAR(50) NULL,
Category NVARCHAR(50) NULL,
[Level] NVARCHAR(50) NULL,
Sublevel NVARCHAR(50) NULL,
Skill NVARCHAR(1000) NULL,
[Long Desc] NVARCHAR(1000) NULL,
Mark NVARCHAR(1000) NULL,
[Cng cre Date] NVARCHAR(1000) NULL,
[Cng sn] NVARCHAR(1000) NULL )
SELECT 'SELECT ' + QUOTENAME([Rec No],'''')+',' + QUOTENAME(CNUM,'''')+',' + QUOTENAME(Category,'''')+',' + QUOTENAME([Level],'''')+',' + QUOTENAME(Sublevel,'''')+',' + QUOTENAME(Skill,'''') +',' + ''''+ [Long Desc] +'''' +','+ QUOTENAME(Mark,'''') +','+ QUOTENAME([Cng cre Date],'''') +','+ QUOTENAME([Cng sn],'''') + ' UNION ALL' FROM GroveStandards.dbo.GWD_MS where category = 'comp'
--===== Insert the test data into the test table
INSERT INTO #mytable ([Rec No], CNUM, Category, [Level], Sublevel, Skill, [Long Desc], Mark, [Cng cre Date], [Cng sn])
SELECT '2901','1,602','Comp','1','1','Standard 1: The student will demonstrate knowledge of basic operations and concepts.','Standard 1: The student will demonstrate knowledge of basic operations and concepts. 1. Apply strategies for identifying and solving routine hardware and software problems that occur during everyday use','1','75,644','505' UNION ALL
SELECT '2902','1,602','Comp','1','2','Standard 1: The student will demonstrate knowledge of basic operations and concepts.','Standard 1: The student will demonstrate knowledge of basic operations and concepts. 2. Demonstrate an understanding of concepts underlying hardware, software, connectivity, and of practical applications to learning and problem solving.','1','75,644','505' UNION ALL
SELECT '2903','1,602','Comp','2','1','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues.','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues. 1. Demonstrate knowledge of current changes in information technologies and the effect those changes have on the workplace and society.','1','75,644','505' UNION ALL
SELECT '2905','1,602','Comp','2','2','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues.','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues. 2. Exhibit legal and ethical behaviors when using information and technology, and discuss consequences of misuse','1','75,644','505' UNION ALL
SELECT '2906','1,602','Comp','2','3','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues.','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues. 3. Research and evaluate the accuracy, relevance, appropriateness, comprehensiveness, and bias of electronic information sources concerning real-world problems.','1','75,644','505' UNION ALL
SELECT '2907','1,602','Comp','3','1','Standard 3: The student will demonstrate knowledge of technology productivity tools.','Standard 3: The student will demonstrate knowledge of technology productivity tools. 1. Use content-specific tools, software, and simulations (e.g., environmental probes, graphing calculators, exploratory environments, Web tools) to support learning and research.','1','75,644','505' UNION ALL
SELECT '2908','1,602','Comp','3','2','Standard 3: The student will demonstrate knowledge of technology productivity tools.','Standard 3: The student will demonstrate knowledge of technology productivity tools. 2. Apply productivity/multimedia tools and peripherals to support personal productivity, group collaboration, and learning throughout the curriculum.','1','75,644','505' UNION ALL
SELECT '2909','1,602','Comp','4','1','Standard 4: The student will demonstrate knowledge of technology communication tools.','Standard 4: The student will demonstrate knowledge of technology communication tools. 1. Design, develop, publish, and present products (e.g., Web pages, videotapes) using technology resources that demonstrate and communicate curriculum concepts to audiences inside and outside the classroom.','1','75,644','505' UNION ALL
SELECT '2911','1,602','Comp','4','2','Standard 4: The student will demonstrate knowledge of technology communication tools.','Standard 4: The student will demonstrate knowledge of technology communication tools. 2. Collaborate with peers, experts, and others using telecommunications and collaborative tools to investigate curriculum-related problems, issues, and information, and to develop solutions or products for audiences inside and outside the classroom.','1','75,644','505' UNION ALL
SELECT '3392','1,602','Comp','5','1','Standard 5: The student will demonstrate knowledge of technology research tools. ','Standard 5: The student will demonstrate knowledge of technology research tools. 1. Use content-specific tools, software, and simulations (e.g., environmental probes, graphing calculators, exploratory environments, Web tools) to support learning and research.','1','75,644','505' UNION ALL
SELECT '3393','1,602','Comp','5','2','Standard 5: The student will demonstrate knowledge of technology research tools.','Standard 5: The student will demonstrate knowledge of technology research tools. 2. Design, develop, publish, and present products (e.g., Web pages, videotapes) using technology resources that demonstrate and communicate curriculum concepts to audiences inside and outside the classroom.','1','75,644','505' UNION ALL
SELECT '3394','1,602','Comp','5','3','Standard 5: The student will demonstrate knowledge of technology research tools.','Standard 5: The student will demonstrate knowledge of technology research tools. 3. Collaborate with peers, experts, and others using telecommunications and collaborative tools to investigate curriculum-related problems, issues, and information, and to develop solutions or products for audiences inside and outside the classroom.','1','75,644','505' UNION ALL
SELECT '3395','1,602','Comp','5','4','Standard 5: The student will demonstrate knowledge of technology research tools.','Standard 5: The student will demonstrate knowledge of technology research tools. 4. Select and use appropriate tools and technology resources to accomplish a variety of tasks and solve problems.','1','75,644','505' UNION ALL
SELECT '3396','1,602','Comp','5','5','Standard 5: The student will demonstrate knowledge of technology research tools.','Standard 5: The student will demonstrate knowledge of technology research tools. 5. Research and evaluate the accuracy, relevance, appropriateness, comprehensiveness, and bias of electronic information sources concerning real-world problems.','1','75,644','505' UNION ALL
SELECT '3397','1,602','Comp','6','1','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools.','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 1. Apply productivity/multimedia tools and peripherals to support personal productivity, group collaboration, and learning throughout the curriculum.','1','75,644','505' UNION ALL
SELECT '3398','1,602','Comp','6','2','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools.','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 2. Design, develop, publish, and present products (e.g., Web pages, videotapes) using technology resources that demonstrate and communicate curriculum concepts to audiences inside and outside the classroom.','1','75,644','505' UNION ALL
SELECT '3399','1,602','Comp','6','3','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools.','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 3. Select and use appropriate tools and technology resources to accomplish a variety of tasks and solve problems.','1','75,644','505' UNION ALL
SELECT '3400','1,602','Comp','6','4','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools.','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 4. Demonstrate an understanding of concepts underlying hardware, software, connectivity, and of practical applications to learning and problem solving.','1','75,644','505' UNION ALL
SELECT '3401','1,602','Comp','6','5','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools.','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 5. Research and evaluate the accuracy, relevance, appropriateness, comprehensiveness, and bias of electronic information sources concerning real-world problems.','1','75,644','505' UNION ALL
SELECT '6649','1,603','Comp','1','1','Standard 1: The student will demonstrate knowledge of basic operations and concepts.','Standard 1: The student will demonstrate knowledge of basic operations and concepts. 1. Apply strategies for identifying and solving routine hardware and software problems that occur during everyday use.','1','75,646','505' UNION ALL
SELECT '6650','1,603','Comp','1','2','Standard 1: The student will demonstrate knowledge of basic operations and concepts.','Standard 1: The student will demonstrate knowledge of basic operations and concepts. 2. Demonstrate an understanding of concepts underlying hardware, software, connectivity, and of practical applications to learning and problem solving.','1','75,646','505' UNION ALL
SELECT '6651','1,603','Comp','2','1','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues.','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues. 1. Demonstrate knowledge of current changes in information technologies and the effect those changes have on the workplace and society','1','75,646','505' UNION ALL
SELECT '6652','1,603','Comp','2','2','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues.','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues. 2. Exhibit legal and ethical behaviors when using information and technology, and discuss consequences of misuse.','1','75,646','505' UNION ALL
SELECT '6653','1,603','Comp','2','3','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues.','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues. 3. Research and evaluate the accuracy, relevance, appropriateness, comprehensiveness, and bias of electronic information sources concerning real-world problems.','1','75,646','505' UNION ALL
SELECT '6654','1,603','Comp','3','1','Standard 3: The student will demonstrate knowledge of technology productivity tools.','Standard 3: The student will demonstrate knowledge of technology productivity tools. 1. Use content-specific tools, software, and simulations (e.g., environmental probes, graphing calculators, exploratory environments, Web tools) to support learning and research.','1','75,646','505' UNION ALL
SELECT '6655','1,603','Comp','3','2','Standard 3: The student will demonstrate knowledge of technology productivity tools.','Standard 3: The student will demonstrate knowledge of technology productivity tools. 2. Apply productivity/multimedia tools and peripherals to support personal productivity, group collaboration, and learning throughout the curriculum.','1','75,646','505' UNION ALL
SELECT '6656','1,603','Comp','4','1','Standard 4: The student will demonstrate knowledge of technology communication tools.','Standard 4: The student will demonstrate knowledge of technology communication tools. 1. Design, develop, publish, and present products (e.g., Web pages, videotapes) using technology resources that demonstrate and communicate curriculum concepts to audiences inside and outside the classroom.','1','75,646','505' UNION ALL
SELECT '6657','1,603','Comp','4','2','Standard 4: The student will demonstrate knowledge of technology communication tools. ','Standard 4: The student will demonstrate knowledge of technology communication tools. 2. Collaborate with peers, experts, and others using telecommunications and collaborative tools to investigate curriculum-related problems, issues, and information, and to develop solutions or products for audiences inside and outside the classroom.','1','75,646','505' UNION ALL
SELECT '6658','1,603','Comp','5','1','Standard 5: The student will demonstrate knowledge of technology research tools. ','Standard 5: The student will demonstrate knowledge of technology research tools. 1. Use content-specific tools, software, and simulations (e.g., environmental probes, graphing calculators, exploratory environments, Web tools) to support learning and research. ','1','75,646','505' UNION ALL
SELECT '6659','1,603','Comp','5','2','Standard 5: The student will demonstrate knowledge of technology research tools. ','Standard 5: The student will demonstrate knowledge of technology research tools. 1. Use content-specific tools, software, and simulations (e.g., environmental probes, graphing calculators, exploratory environments, Web tools) to support learning and research. ','1','75,646','505' UNION ALL
SELECT '6660','1,603','Comp','5','3','Standard 5: The student will demonstrate knowledge of technology research tools. ','Standard 5: The student will demonstrate knowledge of technology research tools. 3. Collaborate with peers, experts, and others using telecommunications and collaborative tools to investigate curriculum-related problems, issues, and information, and to develop solutions or products for audiences inside and outside the classroom. ','1','75,646','505' UNION ALL
SELECT '6661','1,603','Comp','5','4','Standard 5: The student will demonstrate knowledge of technology research tools. ','Standard 5: The student will demonstrate knowledge of technology research tools. 4. Select and use appropriate tools and technology resources to accomplish a variety of tasks and solve problems. ','1','75,646','505' UNION ALL
SELECT '6662','1,603','Comp','5','5','Standard 5: The student will demonstrate knowledge of technology research tools. ','Standard 5: The student will demonstrate knowledge of technology research tools. 5. Research and evaluate the accuracy, relevance, appropriateness, comprehensiveness, and bias of electronic information sources concerning real-world problems. ','1','75,646','505' UNION ALL
SELECT '6663','1,603','Comp','6','1','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools.','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 1. Apply productivity/multimedia tools and peripherals to support personal productivity, group collaboration, and learning throughout the curriculum.','1','75,646','505' UNION ALL
SELECT '6664','1,603','Comp','6','2','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. ','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 2. Design, develop, publish, and present products (e.g., Web pages, videotapes) using technology resources that demonstrate and communicate curriculum concepts to audiences inside and outside the classroom. ','1','75,646','505' UNION ALL
SELECT '6665','1,603','Comp','6','3','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools.','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 3. Select and use appropriate tools and technology resources to accomplish a variety of tasks and solve problems.','1','75,646','505' UNION ALL
SELECT '6666','1,603','Comp','6','4','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. ','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 4. Demonstrate an understanding of concepts underlying hardware, software, connectivity, and of practical applications to learning and problem solving. ','1','75,646','505' UNION ALL
SELECT '6667','1,603','Comp','6','5','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools.','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 5. Research and evaluate the accuracy, relevance, appropriateness, comprehensiveness, and bias of electronic information sources concerning real-world problems.','1','75,646','505' UNION ALL
SELECT '6841','1,604','Comp','1','1','Standard 1: The student will demonstrate knowledge of basic operations and concepts.','Standard 1: The student will demonstrate knowledge of basic operations and concepts. 1. Apply strategies for identifying and solving routine hardware and software problems that occur during everyday use.','0','75,654','505' UNION ALL
SELECT '6842','1,604','Comp','1','2','Standard 1: The student will demonstrate knowledge of basic operations and concepts.','Standard 1: The student will demonstrate knowledge of basic operations and concepts. 2. Demonstrate an understanding of concepts underlying hardware, software, connectivity, and of practical applications to learning and problem solving.','0','75,654','505' UNION ALL
SELECT '6843','1,604','Comp','2','1','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues.','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues. 1 Demonstrate knowledge of current changes in information technologies and the effect those changes have on the workplace and society.','0','75,654','505' UNION ALL
SELECT '6844','1,604','Comp','2','2','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues.','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues. 2. Exhibit legal and ethical behaviors when using information and technology, and discuss consequences of misuse.','0','75,654','505' UNION ALL
SELECT '6845','1,604','Comp','2','3','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues.','Standard 2: The student will demonstrate knowledge of social, ethical, and human issues. 3. Research and evaluate the accuracy, relevance, appropriateness, comprehensiveness, and bias of electronic information sources concerning real-world problems.','0','75,654','505' UNION ALL
SELECT '6846','1,604','Comp','3','1','Standard 3: The student will demonstrate knowledge of technology productivity tools.','Standard 3: The student will demonstrate knowledge of technology productivity tools. 1. Use content-specific tools, software, and simulations (e.g., environmental probes, graphing calculators, exploratory environments, Web tools) to support learning and research.','0','75,654','505' UNION ALL
SELECT '6847','1,604','Comp','3','2','Standard 3: The student will demonstrate knowledge of technology productivity tools.','Standard 3: The student will demonstrate knowledge of technology productivity tools. 2. Apply productivity/multimedia tools and peripherals to support personal productivity, group collaboration, and learning throughout the curriculum.','0','75,654','505' UNION ALL
SELECT '6848','1,604','Comp','4','1','Standard 4: The student will demonstrate knowledge of technology communication tools.','Standard 4: The student will demonstrate knowledge of technology communication tools. 1. Design, develop, publish, and present products (e.g., Web pages, videotapes) using technology resources that demonstrate and communicate curriculum concepts to audiences inside and outside the classroom. ','0','75,654','505' UNION ALL
SELECT '6849','1,604','Comp','4','2','Standard 4: The student will demonstrate knowledge of technology communication tools.','Standard 4: The student will demonstrate knowledge of technology communication tools. 2. Collaborate with peers, experts, and others using telecommunications and collaborative tools to investigate curriculum-related problems, issues, and information, and to develop solutions or products for audiences inside and outside the classroom.','0','75,654','505' UNION ALL
SELECT '6850','1,604','Comp','5','1','Standard 5: The student will demonstrate knowledge of technology research tools.','Standard 5: The student will demonstrate knowledge of technology research tools. 1. Use content-specific tools, software, and simulations (e.g., environmental probes, graphing calculators, exploratory environments, Web tools) to support learning and research. ','0','75,654','505' UNION ALL
SELECT '6851','1,604','Comp','5','2','Standard 5: The student will demonstrate knowledge of technology research tools.','Standard 5: The student will demonstrate knowledge of technology research tools. 2. Design, develop, publish, and present products (e.g., Web pages, videotapes) using technology resources that demonstrate and communicate curriculum concepts to audiences inside and outside the classroom.','0','75,654','505' UNION ALL
SELECT '6852','1,604','Comp','5','3','Standard 5: The student will demonstrate knowledge of technology research tools.','Standard 5: The student will demonstrate knowledge of technology research tools. 3. Collaborate with peers, experts, and others using telecommunications and collaborative tools to investigate curriculum-related problems, issues, and information, and to develop solutions or products for audiences inside and outside the classroom.','0','75,654','505' UNION ALL
SELECT '6853','1,604','Comp','5','4','Standard 5: The student will demonstrate knowledge of technology research tools.','Standard 5: The student will demonstrate knowledge of technology research tools. 4. Select and use appropriate tools and technology resources to accomplish a variety of tasks and solve problems.','0','75,654','505' UNION ALL
SELECT '6854','1,604','Comp','5','5','Standard 5: The student will demonstrate knowledge of technology research tools.','Standard 5: The student will demonstrate knowledge of technology research tools. 5. Research and evaluate the accuracy, relevance, appropriateness, comprehensiveness, and bias of electronic information sources concerning real-world problems.','0','75,654','505' UNION ALL
SELECT '6855','1,604','Comp','6','1','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools.','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 1. Apply productivity/multimedia tools and peripherals to support personal productivity, group collaboration, and learning throughout the curriculum. ','0','75,654','505' UNION ALL
SELECT '6856','1,604','Comp','6','2','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools.','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 2. Design, develop, publish, and present products (e.g., Web pages, videotapes) using technology resources that demonstrate and communicate curriculum concepts to audiences inside and outside the classroom.','0','75,654','505' UNION ALL
SELECT '6857','1,604','Comp','6','3','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. ','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 3. Select and use appropriate tools and technology resources to accomplish a variety of tasks and solve problems.','0','75,654','505' UNION ALL
SELECT '6858','1,604','Comp','6','4','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools.','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 4. Demonstrate an understanding of concepts underlying hardware, software, connectivity, and of practical applications to learning and problem solving.','0','75,654','505' UNION ALL
SELECT '6859','1,604','Comp','6','5','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools.','Standard 6: The student will demonstrate knowledge of technology problem-solving and decision-making tools. 5. Research and evaluate the accuracy, relevance, appropriateness, comprehensiveness, and bias of electronic information sources concerning real-world problems.','0','75,654','505'
SELECT [Rec No], Category, [Level], Sublevel, RTRIM(LTRIM(SUBSTRING(Skill,1,90))) AS Skill,
ROW_NUMBER() OVER(PARTITION BY Category, [Level], Sublevel, RTRIM(LTRIM(SUBSTRING(Skill,1,90))) ORDER BY Category, [Level], Sublevel, RTRIM(LTRIM(SUBSTRING(Skill,1,90))))
FROM #mytable
where category = 'comp'
if you compare the two, you can easily see the differences. There are maybe 5 more columns i can add that is within the original table. But i don't really understand why another column would affect the results if it is not included in the query and if there are no triggers or anything.
March 10, 2010 at 10:49 am
I'm looking at the data in the table and i see the problem (I THINK), RTRIM and LTRIM did not remove the square characters at the end. Evidently they didn't transfer over into the temp table when i copied the data as above.
I don't know how to remove them except manually.
Removing the square characters seems to fix the problem. What a waste of time!!!
Is there anything I can do to avoid this in the future? it is ashamed that this affects the resultset, but there is no other way to identify the problem--NOT EVEN COPYING THE DATA to another table. Seems stupid to me.
Thanks guys!
March 10, 2010 at 4:45 pm
Jacob Pressures (3/10/2010)
I'm looking at the data in the table and i see the problem (I THINK), RTRIM and LTRIM did not remove the square characters at the end. Evidently they didn't transfer over into the temp table when i copied the data as above.I don't know how to remove them except manually.
Removing the square characters seems to fix the problem. What a waste of time!!!
Is there anything I can do to avoid this in the future? it is ashamed that this affects the resultset, but there is no other way to identify the problem--NOT EVEN COPYING THE DATA to another table. Seems stupid to me.
Thanks guys!
The square characters are very likey carriage returns and line feeds. You can get rid of them quite easily.
UPDATE yourtable
SET yourcolumn = REPLACE(REPLACE(yourcolumn,CHAR(13),''),CHAR(10),'')
CHAR(13) is a carriage return.
CHAR(10) is a line feed.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply