search a range ofchars - 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 2000 Forums

SQL Server Development (2000)

search a range ofchars

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-06-29 : 15:47:32

i have a col of type nvarchar(15)
and it contains usually number in range 0..9
but i found that illigeal chars have entered and i want to find them
how can i do that?
thnaks i nadvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 15:51:32

[code]
SELECT *
FROM MyTable
WHERE PATINDEX('%[^0-9]%', MyField) > 0
OR MyField IS NULL[/code]
Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-06-29 : 16:00:26

thnaks alot

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-30 : 02:35:52


SELECT  *
FROM    MyTable
WHERE   MyField LIKE '%[^0-9]%'
        OR MyField IS NULL

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-06-30 : 03:47:29

Heheheh ... or


SELECT  PKCol1, PKCol2, ... -- Only PK columns here

FROM    MyTable
WHERE   MyField LIKE '%[^0-9]%'
--        OR MyField IS NULL  -- Not a requirement, presumably?

where you have an index starting with MyField and covering the PKColumns - which will be the case if you just have MyField in your index AND you the PRIMARY KEY is clustered.

Should be nice and quick then!

If you need other columns you could use this as a nested sub-select, and I expect it would be quicker than "straight coding".

Mind you, unless you have in excess of 10M rows this is will be all rather academic!

Of, and pelegk2: stick a constraint on that column to stop the blasted users putting rubbish in there!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-30 : 04:15:36

>>SELECT PKCol1, PKCol2, ... -- Only PK columns here

Do you think this will improve performance if more than one column is part of PK and when Like '%[]%' prevents make use of Index?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-06-30 : 04:37:15

"Like '%[]%' prevents make use of Index?"

I think the index can be used to assess the matching rows, provided that it covers the query - so if the column being tested is the first in the index, and only PK columns are SELECTed, and they too are in the index (which would be the case for a secondary index on a table using a clustered index for the PK, other you could add them of course!), then the query is "covered" by the index.

But I haven't tested it, so I may have to eat my words as yet!

Kristen

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-07-07 : 03:37:54

is there a diffrence in the performance of PATINDEX verses LIKE
when for example i look for a string in col with text?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-07 : 04:05:56

If you search for string starting with Search string, LIKE will make use of index whereas Charindex and PatIndex dont

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-07 : 11:31:53

quote:
Originally posted by pelegk2

i have a col of type nvarchar(15)
and it contains usually number in range 0..9
but i found that illigeal chars have entered and i want to find them
how can i do that?
thnaks i nadvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

You should add a check constraint to keep the bad data out of the table:


ALTER TABLE [dbo].[MY_TABLLE]
ADD CONSTRAINT 
[CK__MY_TABLE__MY_COLUMN_IS_VALID]
CHECK	(
	case
	-- Fail if not characters 0 to 9
	when MY_COLUMN like '%[^0123456789]%'
	then 0
	else 1 end = 1
	)

CODO ERGO SUM

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-07-09 : 01:32:27

hi Michael Valentine Jones
why do u do this part :

end = 1

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-10 : 02:47:05

quote:
Originally posted by pelegk2

hi Michael Valentine Jones
why do u do this part :
end = 1

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

It is for comparing the result of Expression

[code]ALTER TABLE [dbo].[MY_TABLLE]
ADD CONSTRAINT
[CK__MY_TABLE__MY_COLUMN_IS_VALID]
CHECK (
case
-- Fail if not characters 0 to 9
when MY_COLUMN like '%[^0123456789]%'
then 0
else 1 end --Result of Expression
= 1
)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources