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 MontyWeb Ninja at largeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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?
Big MontyWeb Ninja at largeAuthor Commented:
yes. for example, any records that have chinese / japanese characters in them
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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
Big MontyWeb Ninja at largeAuthor 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
ste5anSenior DeveloperCommented:
What conditions?? What about a concise and complete example as runnable T-SQL script... I don't like guessing that much..
Big MontyWeb Ninja at largeAuthor 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) アプリケーター
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

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 MontyWeb Ninja at largeAuthor 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
pcelbaCommented:
The function was updated in the meantime...
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.