Need a clarification in a query

[manohars]

I have written the following query to list the rows which have invalid characters in that column

SELECT * from titles where convert(varchar(255), [title]) like
'%[^-\^_<>`~{} :[",#$%&!?;+0-9a-zA-Z@/*().]%' AND convert(varchar(255), [title]) NOT LIKE '%]%'

List of valid characters are [^-\^_<>`~{} :[",#$%&!?;+0-9a-zA-Z@/*().]. But this query returns the rows, which have invalid characters. How does this happen?

Thanks in Advance,
Manohar

 

[manohars]

This query is given with the list of character in the like clause. How does it return rows with invalid chars.

Thanks,
Manohar

 

[SQLBill]

Can you give us a sample of what you are getting returned and what you really want to get returned?

Also, what datatype is Title?

-SQLBill

 

[manohars]

This query is run in pubs database only. You can get the result from there. It returns only one row, which has single quote in the title column.

Thanks,
Manohar

 

[dbomrrsm]

SELECT * from titles where   title like 
'%[^-\^_<>`~{} :[",#$%&?;!''+0-9a-zA-Z@/*().]%'
AND title NOT LIKE '%]%'

returns nothing as I have added in '' to allow for the ' in the The Busy Executive's Database Guide.

Wwhat are you trying to achieve - and BTW there is no need to convert as the datatype of title is already varchar.


[blue]DBomrrsm[/blue]

 

[manohars]

I am trying to write a stored procedure, which will search all the columns of all the tables in a database and will give the list of columns(in tables) which have data, that contains characters other than those mentioned above.

The query given above is part of a cursor. Since the same query is used for all the columns, it is searched after converting it to varchar.

Thanks,
Manohar

 

[dbomrrsm]

The below allows you to search all columns of all tables in a DB to find text string. I am sure you will be able to modify for your use.

CREATE PROC SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
BEGIN

	CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END	
	END

	SELECT ColumnName, ColumnValue FROM #Results
END


--USE
--To search all columns of all tables in Pubs database for the keyword "Computer" 
--EXEC SearchAllTables 'Computer'
--GO

[blue]DBomrrsm[/blue]