SQL statement is being truncated by Char(0)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL statement is being truncated by Char(0)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-10-31 : 07:16:13

Rick writes "I am trying to search columns in a table for characters CHAR(0) thru CHAR(31). I reviewed your procedure to search for a string, but this is a little different. Here is what I have:

SET @Dyna_SQL_String2 = 'INSERT into ANLS_IdentifyEmbeddedCharacters'
+ ' SELECT ' + '''' + @Table_Name_In + ''', ' + '''' + @Current_Field_name + ''', ' + 'CaseMasterID' + ' FROM ' + @Table_Name_In + ' WHERE ' + ' patindex(''%[' + char(0) + '-' + char(31) + ']%'',' + '' + @Current_Field_name + '' + ') > 0'

The problem is that the @Dyna_SQL_String2 is getting truncated as soon as the char(0) is encountered.

Thoughts?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-31 : 07:18:25

Since SQL Server is mostly written in C/C++, and since char(0) terminates C strings, there's no way to change this behavior.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 07:26:23

SET @Dyna_SQL_String2 = 'INSERT into ANLS_IdentifyEmbeddedCharacters SELECT [' + @Table_Name_In + '], [' + @Current_Field_name + '], CaseMasterID FROM [' + @Table_Name_In + '] WHERE dbo.fnLessThanSpace([' + @Current_Field_name + ']) = 1'

CREATE FUNCTION dbo.fnHasLessThanSpace
(
	@Text VARCHAR(8000)
)
RETURNS BIT
AS

BEGIN
	DECLARE	@Index INT

	SELECT	@Index = 0

	WHILE @Index >= 0 AND @Index <= 31
		IF CHARINDEX(CHAR(@Index), @Text) > 0
			SELECT	@Index = -1
		ELSE
			SELECT	@Index = @Index + 1

	RETURN CASE WHEN @Index < 0 THEN 1 ELSE 0 END
END

Peter Larsson
Helsingborg, Sweden

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-31 : 07:54:37

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 08:12:29

Maybe a simpler method?

SET @Dyna_SQL_String2 = 'INSERT into ANLS_IdentifyEmbeddedCharacters SELECT [' + @Table_Name_In + '], [' + @Current_Field_name + '], CaseMasterID FROM [' + @Table_Name_In + '] WHERE PATINDEX(''%[' + CHAR(1) + '-' + CHAR(31) ']%'', [' + @Current_Field_name + ']) > 0 OR CHARINDEX(CHAR(0), [' + @Current_Field_name + ']) > 0'

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources