Post reply

Trying to print records that have only english characters

  • July 17, 2015 at 12:13 pm

    #305883

    I have a small table that looks like this

    create table Test(ID number, Name nvarchar(500));

    insert into Test(1,'abc testing');

    insert into Test(2,'abc include persian آنا اسمیت');

    insert into Test(3,'mnp testing');

    insert into Test(4,'abc include Russian Джон Тед');

    I want to get records that have only english characters i.e ID=1 and 3 only. I tried

    select * from Test where Name like '%[a-zA-Z0-9]%'

    but this will return all 4 records. How can I accomplish this?

    Thanks

    R

  • July 17, 2015 at 12:43 pm

    #1813402

    You need a double negative condition. The only problem is that you need to include all possible characters as I did with the space.

    SELECT *

    FROM Test

    WHERE Name NOT LIKE '%[^a-zA-Z0-9 ]%'

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 17, 2015 at 12:53 pm

    #1813404

    This is a different way of accomplish this task, but you might need to correct the sample data to be able to check it.

    --Note the N before the string literals to define them as unicode strings

    --Without it, it converts the strings to varchar before converting them again to nvarchar

    create table Test(ID int, Name nvarchar(500));

    insert into Test VALUES (1,N'abc testing'),

    (2,N'abc include persian ??? ?????')

    ,(3,N'mnp testing')

    ,(4,N'abc include Russian ???? ???');

    SELECT *

    FROM Test

    WHERE Name = CAST( Name AS varchar(500))

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply