do spaces in sql tables fubar queries?

Question

I'm having an issue I'm generating sudoku puzzles and the solution to the puzzle are being stored in this format: xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx

8 spaces between the 9 sets, I cannot seem to query the row, it returns a blank row..

Is this because there is spaces? If I do a select where solution like 'xxxxxxxxx%' then the row returns properly..

Did I just generate 50,000 rows for nothing :( If so how should I fix this spacing problem?

edit here is the query:

select * from sudoktion_puzzles 
where
p_solution='582917643 467283195 391546278 159362784 
674851329 823794561 216438957 938675412 745129836'

Answer 1

In the query you quoted, you have two spaces before the second-to-last term (the one beginning with '9'). If that's your actual query code but not the actual value in the table, it would explain your problem.

Answer 2

About the only thing peculiar about spaces you really need to remember about SQL Server (ANSI compliant) is that trailing spaces are not factored into LEN() or equality comparisons.

Is there anything peculiar about your data types?

Are they long enough to hold the entire string (at least 89 characters)?

If you want to get rid of the spaces, you can simply remove the spaces with:

UPDATE sudoktion_puzzles
SET p_solution = REPLACE(p_solution, ' ', '')

Answer 3

Double check that there isn't a second space anywhere, before or after.

Run

SELECT * FROM sudoktion_puzzles
 WHERE LEN(p_solution) <> 89

and see if you return the row you can't get otherwise. If so then you have extra spaces.

Answer 4

Hey guys those spaces were actually newline/enter characters this fixed it:

UPDATE sudoktion_puzzles
SET p_board = REPLACE(REPLACE(REPLACE(p_board, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')