sql server - retrieve all records that contain multi-byte data

is there a way to determine all rows in my table that contain multi-byte data? this is for MS Sql Server 2008 / 2012
LVL 34
Big MontySenior Web Developer / CEO of ExchangeTree.org Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
What does "multi-byte" data mean?  Do you mean unicode strings?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
yes. for example, any records that have chinese / japanese characters in them
0
ste5anSenior DeveloperCommented:
Please rephrase your question..

But I guess you should take a look at LEN vs. DATALENGTH:

 
DECLARE @Sample TABLE ( Test NVARCHAR(255) );

INSERT INTO @Sample
VALUES	( N'1' ),
	( N'क्षि' );

SELECT	LEN(Test),
	DATALENGTH(Test),
	Test
FROM	@Sample;

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Brian CroweDatabase AdministratorCommented:
SELECT O.name AS TableName, C.name AS ColumnName,
      T.name AS TypeName, C.max_length
FROM sys.objects AS O
INNER JOIN sys.columns AS C
      ON O.object_id = C.object_id
INNER JOIN sys.types AS T
      ON C.user_type_id = T.user_type_id
WHERE O.type = 'U'
      AND C.user_type_id = 231
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
@Brian - I'm looking for a query to tell me if certain records in my table contains any multi-byte chars, i.e. chinese chars. Most of the records contains plain english, but i have a few that contain those special chars. Your query tells me the data type for each field, which is not what I'm looking for.

@st5an - not sure how you else you want me to re-phrase the question...the query you gave me doesn't really differentiate from the two conditions I'm looking for
0
ste5anSenior DeveloperCommented:
What conditions?? What about a concise and complete example as runnable T-SQL script... I don't like guessing that much..
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
in pseudo code, it would be something like:

select * from table where field contains any multi-byte char

the field in question would contain something like

シュアビード レデュースドキャビティ(RC) アプリケーター
0
pcelbaCommented:
Let suppose your multibyte characters are in nvarchar columns. Then you may use following function:
CREATE FUNCTION dbo.HasDoubleByte 
(
	@InpStr nvarchar(4000)   -- this line was updated
)
RETURNS bit
AS
BEGIN
	DECLARE @RetVal bit, @i int, @BinStr varbinary(8000)
	
	SELECT @i = 2, @BinStr = CAST(@InpStr AS varbinary(8000)), @RetVal = 0
	
	WHILE @i <= LEN(@BinStr) AND @RetVal = 0
	BEGIN
	  IF SUBSTRING(@BinStr, @i, 1) <> 0x00
	    SET @RetVal = 1
	  SET @i = @i + 2
	END
	
	RETURN @RetVal

END
GO

Open in new window

To recognize multibyte data in nvarchar column simply write following query:

SELECT SomeID, YourColumn FROM YourTable WHERE dbo.HasDoubleByte(YourColumn) = 1

Update: Bug fix in the function
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
i get a zero returned from the function when I run the query (after creating the function).

SELECT Cache_Obj_Filename, dbo.HasDoubleByte(Cache_Obj_Filename) FROM Data where .......

the record shows up, but so do all of the other records
0
pcelbaCommented:
The function was updated in the meantime...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.