Filtering Rogue Chars

[robert030975]

HI

I was wondering if there is any way of filtering out rogue chars the only thing is that has to be considered is a double barrelled name Peter-Paul Thomas.

FirstName Surname
Peter Èajèyc Thomas


Many Thanks

Robert

 

[markros]

select * from myTable where myField not like
'%[0-9a-z]%

PluralSight Learning Library

 

[robert030975]

HI Markos

That doesn't work

ie if you have

FirstName Surname
Peter Èajèyc Thomas
Peter Mason
Brad-Jones Smith

and use the select * from myTable where myField like'%[0-9a-z]% it picks up all 3 instead of the first record.

 

[bborissov]

DECLARE @Test TABLE (MyField nvarchar(200))
INSERT INTO @Test VALUES (N'Peter Èajèyc  Thomas')
INSERT INTO @Test VALUES (N'Peter           Mason')
INSERT INTO @Test VALUES (N'Brad-Jones      Smith')
           
           
select * from @Test where myField like '%^[0-9a-zA-Z]%'

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[robert030975]

Hi Boris

If I do as stated


DECLARE @Test TABLE (MyField nvarchar(200))
INSERT INTO @Test VALUES (N'Peter Èajèyc Thomas')
INSERT INTO @Test VALUES (N'Peter Mason')
INSERT INTO @Test VALUES (N'Brad-Jones Smith')

select * from @Test where myField like '%^[0-9a-zA-Z]%'

it doesn't bring up Peter it brings nothing. I need to try and trap Peter.

Thanks

 

[bborissov]

DECLARE @Test TABLE (MyField nvarchar(200))
INSERT INTO @Test VALUES (N'Peter Èajèyc  Thomas')
INSERT INTO @Test VALUES (N'Peter           Mason')
INSERT INTO @Test VALUES (N'Brad-Jones      Smith')                      

select * from @Test where myField COLLATE Cyrillic_General_CI_AI like '%^[0-9a-zA-Z]%'

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[tsuji]

If I understand correctly, firstname and surname are two fields(?). Suppose the filter is restricted on the firstname, you can do this?
[tt]
select * from [blue]tablename[/blue] where FirstName not like '%[^0-9a-zA-Z\-]%'
[/tt]
The filter is still quite loose and not very restrictive. (For instance, it allows "PeterPaul-" which is not quite correct as a first name.)

 

[markros]

In my query you missed that I used NOT LIKE!

PluralSight Learning Library

 

[bborissov]

Yeah,
that was my first thought, but all the records are LIKE '%[0-9a-zA-Z]%'
Just because every record has at least one English char.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.