[Square Brackets] - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Square Brackets]

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-04 : 10:53:14

This is part of a select statement

WHERE derivative='2.2 CRTD CDX 5dr [7 Seats]'

Its not returning any results, when it should. How do I specify that the brackets are actually text not special characters?

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-04 : 10:56:14

when using = brackets aren't special chars.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 10:57:27

It works.

DECLARE	@Test TABLE (info VARCHAR(200))

INSERT	@Test
SELECT	'2.2 CRTD CDX 5dr [7 Seats]' UNION ALL
SELECT	'2.3 CRTD CDX 5dr [7 Seats]'

select * from @test
where info = '2.3 CRTD CDX 5dr [7 Seats]'

You are maybe using a CASE SENSITIVE collation?

It is only with PATINDEX and LIKE brackets are treated differently.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 10:58:13

May be spaces aren't really spaces (ASCII 32)? They might be "hard-spaces" (ASCII 160)?

E 12°55'05.25"
N 56°04'39.16"

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-04 : 10:59:55

It should work the way it is, you may have the string mistyped?

------------------------
Future guru in the making.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 11:03:20

Try this code to see which characters you have in your table

SELECT		SUBSTRING(t.derivative, v.Number, 1) AS Character,
		ASCII(SUBSTRING(t.derivative, v.Number, 1)) AS AsciiValue, 
		COUNT(*) AS Items
FROM		master..spt_values AS v
INNER JOIN	<yourtablenamehere> AS t ON LEN(t.derivative) > v.Number
WHERE		v.Type = 'p'
		AND v.Number > 0
GROUP BY	ASCII(SUBSTRING(t.derivative, v.Number, 1)),
		SUBSTRING(t.derivative, v.Number, 1)
ORDER BY	2

Watch out for 32 and 160.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources