Geek Answers Handbook

How to select strings containing non-English characters in sql server 2005 (it should only filter non-English characters, not special characters)

How my table contains non-English (contains characters in different languages) characters and special characters in a column. I only need filters other than English. It should filter out any special characters.

I tried using different methods for filtering, but could not filter out multiple lines. someone please help me with this. Thanks in advance.

for example: the LOCATION column name contains the following lines:

line 1 : துய இம்மானுவேல் தேவாலயம், North Street, Idianviliai, Tamil Nadu, India

line 2 : Dr. Hakim M. Asgar Ali ROY MEDICAL CENTER ™ Unani Clinic In Kerala, India, Thycaud Hospital Road, Opp. Amritha Hotel, Thycaud.PO, Thiruvananthapuram, Kerala, India

line 3 : ಕಾಳಿಕ ಾಂ ಬ ದೇವಿ ದೇವಸ್ಥಾನ, Shivaji Nagar, Davanger, Karnataka, India

As stated above, characters are in many languages. can anyone help me choose only row 2 .

+4
source share
3 answers

T-SQL string processing capabilities are rather rudimentary.

If non-English fields are different using Unicode UTF-16, you can try something like

SELECT * FROM MyTable WHERE MyField = Cast(MyField AS VARCHAR)

to pull out only lines that can be expressed in UTF-8.

, , , , - , :

CREATE FUNCTION IsAllowed (@input VARCHAR(MAX)) RETURNS BIT
-- Returns 1 if string is allowed, 0 otherwise.
-- Usages: SELECT dbo.IsAllowed('Hello'); -- returns 1
--         SELECT dbo.IsAllowed('Hello, world!'); -- returns 0
-- Note CHARINDEX is not case sensitive so @allowables doesn't need both.
--      VARCHAR(MAX) is different under SQL Server 2005 than 2008+
---     and use of defined VARCHAR size might be necessary.
AS
BEGIN
  DECLARE @allowables char(26) = 'abcdefghijklmnopqrstuvwxyz';
  DECLARE @allowed int = 0; 
  DECLARE @index int = 1;
  WHILE @index <= LEN(@input)
    BEGIN
    IF CHARINDEX(SUBSTRING(@input,@index,1),@allowables)=0
      BEGIN
      SET @allowed = 0;
      BREAK;
      END
    ELSE
      BEGIN
      SET @allowed = 1;
      SET @index = @index+1;
      END
    END
  RETURN @allowed
END

SELECT, :

SELECT * FROM MyTable WHERE dbo.IsAllowed(MyField) = 1

, (dbo ) .

T-SQL ,

+6

.

     select ID, LATITUDE, LONGITUDE, REFERENCE, LOCATION, VALIDATE,
     patindex('%[^ !-~()"]%' COLLATE Latin1_General_BIN,LOCATION) as [Position],
     substring(LOCATION,patindex('%[^ !-~()"]%' COLLATE Latin1_General_BIN,LOCATION),1) as [InvalidCharacter],
     ascii(substring(LOCATION,patindex('%[^ !-~()"]%' COLLATE Latin1_General_BIN,LOCATION),1)) as [ASCIICode]from  dbo.RADAR_SEARCH where patindex('%[^ !-~()"]%' COLLATE Latin1_General_BIN,LOCATION) >0

EDIT1:

, .

. MS-SQL.

+2

If you have all the special characters allowed, the following select statement should select all columns with only English and special characters allowed:

select column_name from table_name where column_name like '%[^a-z, .-™]%';

You can add all valid special characters to square brackets.

+2
source

More articles:


All Articles