Find double byte character data

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 25, 2019 at 4:42 am

  #376873

  I have a table which contains double byte character and single byte character records in the FORMAT column. I want to find which record contains double byte character? Is there any query to find the double byte character records?

  DDL COMMANDS

  CREATE TABLE Product_Details
(Language varchar(2), format NVarchar(max))

  --Populate Product_Details with single byte and --double byte in Format column

  INSERT INTO Product_Details (Language, format)
VALUES ('EN', 'Basavaraj')

  INSERT INTO Product_Details (Language, format)
VALUES ('JP', N'尊敬卿')

  INSERT INTO Product_Details (Language, format)
VALUES ('EN', 'Biradar')

  Insert into Product_Details (Language,format)
VALUES('CN','你好')

  Now i want to list the records which format column contains double byte character data(for example Japanese language,Chinese language records..).The table contains lot of Chinese and Japanese language records.so i am trying to find out which format column contains this records.

  Required Output

  Language     Format
JP           尊敬卿
CN           你好

  I used below query to find the records,but it is showing all the records(EN,JP,CN)

  SELECT DISTINCT F_LANGUAGE
FROM Product_Details
WHERE Format != CAST(Format AS VARCHAR(MAX))
   

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 25, 2019 at 4:58 am

  #2020083

  SELECT Language, format
FROM Product_Details
WHERE TRY_CONVERT(varchar(max),format) = REPLICATE('?',LEN(format))

  John

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 25, 2019 at 6:12 am

  #2020094

  it is showing error message varchar is not  recognized built-in function name.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 25, 2019 at 6:49 am

  #2020102

  You've posted in the SQL Server 2012 forum.  Are you actually using SQL Server 2008 R2 or below?

  John

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 30, 2019 at 1:29 am

  #2020528

  i checked my version.Please find the details.

  select @@version

  Microsoft SQL Server 2014 (SP1-CU13) (KB4019099) - 12.0.4522.0 (X64) Jun 28 2017 17:36:31 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 <X64>

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 30, 2019 at 2:22 am

  #2020530

  ALTER DATABASE myDatabase SET COMPATIBILITY_LEVEL = 120;

  Don't do this on the production server unless you really know what you're doing.

  John

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  January 30, 2019 at 3:30 am

  #2020534

  Here is a simple solution
  😎
  
USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @Product_Details TABLE
([Language] varchar(2), [format] NVARCHAR(max))

  INSERT INTO @Product_Details ([Language], [format])
VALUES
('EN', N'Basavaraj')
,('JP', N'???')
,('EN', N'Biradar')
,('CN', N'??') ;

  SELECT
  PD.[Language]
 ,PD.[format]
FROM @Product_Details PD
WHERE UNICODE(LEFT(PD.[format],1)) > 255;


  Output
  
Language format
-------- -------
JP       ???
CN       ??

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 30, 2019 at 4:11 am

  #2020537

  John Mitchell-245523 - Wednesday, January 30, 2019 2:22 AM

  ALTER DATABASE myDatabase SET COMPATIBILITY_LEVEL = 120;

  Don't do this on the production server unless you really know what you're doing.

  John

  I cannot do this in our remote server machine.So any method to achieve this.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 30, 2019 at 4:17 am

  #2020538

  jkramprakash - Wednesday, January 30, 2019 4:11 AM

  John Mitchell-245523 - Wednesday, January 30, 2019 2:22 AM

  ALTER DATABASE myDatabase SET COMPATIBILITY_LEVEL = 120;

  Don't do this on the production server unless you really know what you're doing.

  John

  I cannot do this in our remote server machine.So any method to achieve this.

  Why not?  Why have a SQL Server 2014 server and run databases at SQL Server 2008 compatibility level?

  Eirikur's solution should work despite the compatibility level.

  John

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 30, 2019 at 4:27 am

  #2020539

  Eirikur Eiriksson - Wednesday, January 30, 2019 3:30 AM

  Here is a simple solution
  😎
  
  USE TEEST;
  GO
  SET NOCOUNT ON;

  DECLARE @Product_Details TABLE
  ([Language] varchar(2), [format] NVARCHAR(max))

  INSERT INTO @Product_Details ([Language], [format])
  VALUES
  ('EN', N'Basavaraj')
  ,('JP', N'尊敬å¿')
  ,('EN', N'Biradar')
  ,('CN', N'你好') ;

  SELECT
  PD.[Language]
 ,PD.[format]
FROM @Product_Details PD
WHERE UNICODE(LEFT(PD.[format],1)) > 255;


  Output
  
Language format
-------- -------
JP       å°Šæ•¬å¿
CN       ä½ å¥½

  I tired above query in our database.It is picking the Language 'EN' also.
  so i run the below query.

  SELECT
max(( UNICODE(LEFT(PD.[format],1))))
FROM Product_Details PD where language='EN'

  OUTPUT
--------
26460

  It is greater then 255.so it is picking the language 'EN' also.

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 30, 2019 at 4:33 am

  #2020541

  John Mitchell-245523 - Wednesday, January 30, 2019 4:17 AM

  jkramprakash - Wednesday, January 30, 2019 4:11 AM

  John Mitchell-245523 - Wednesday, January 30, 2019 2:22 AM

  ALTER DATABASE myDatabase SET COMPATIBILITY_LEVEL = 120;

  Don't do this on the production server unless you really know what you're doing.

  John

  I cannot do this in our remote server machine.So any method to achieve this.

  Why not?  Why have a SQL Server 2014 server and run databases at SQL Server 2008 compatibility level?

  Eirikur's solution should work despite the compatibility level.

  John

  it is like production server.so i am not able to execute this ALTER  command.It needs lot of procedures to execute this command.

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  January 30, 2019 at 5:45 am

  #2020548

  jkramprakash - Wednesday, January 30, 2019 4:27 AM

  Eirikur Eiriksson - Wednesday, January 30, 2019 3:30 AM

  Here is a simple solution
  😎
  
  USE TEEST;
  GO
  SET NOCOUNT ON;

  DECLARE @Product_Details TABLE
  ([Language] varchar(2), [format] NVARCHAR(max))

  INSERT INTO @Product_Details ([Language], [format])
  VALUES
  ('EN', N'Basavaraj')
  ,('JP', N'尊敬å¿')
  ,('EN', N'Biradar')
  ,('CN', N'你好') ;

  SELECT
  PD.[Language]
 ,PD.[format]
FROM @Product_Details PD
WHERE UNICODE(LEFT(PD.[format],1)) > 255;


  Output
  
Language format
-------- -------
JP       å°Šæ•¬å¿
CN       ä½ å¥½

  I tired above query in our database.It is picking the Language 'EN' also.
  so i run the below query.

  SELECT
max(( UNICODE(LEFT(PD.[format],1))))
FROM Product_Details PD where language='EN'

  OUTPUT
--------
26460

  It is greater then 255.so it is picking the language 'EN' also.

  Character 26469 is æœ so something is funny in your data.
  😎