CODEWITHSUNDEEP
sqlsql-servert-sql
jr3
jr3

Reputation: 915

do spaces in sql tables fubar queries?

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'

Upvotes: 0

Views: 231

Answers (4)

jr3
jr3

Reputation: 915

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), '')

Upvotes: 1

James Cronen
James Cronen

Reputation: 5763

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.

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89661

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, ' ', '')

Upvotes: 1

Larry Lustig
Larry Lustig

Reputation: 50970

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.

Upvotes: 4

Related Questions