Search query ignore all non-alphabetic characters from string

I have a table product:

ID NAME

1  abcde

2 ab-cde

3 ab cde

4 abcd,e

5 bcd-e

6 ab cd

If I run the query

SELECT * FROM product

WHERE NAME like ‘%abcde%’

I will get:

ID NAME

1  abcde

But I want to see this output:

ID NAME

1  abcde

2 ab-cde

3 ab cde

4 abcd-e

Means output should ignore all space, comma, semicolon, hi-fen.

 

Solution:

Steps:

1.

CREATE FUNCTION [dbo].[RemoveChars](@Input varchar(1000))
RETURNS VARCHAR(1000)
BEGIN
DECLARE @pos INT
SET @Pos = PATINDEX(‘%[^a-z^0-9]%’,@Input)
WHILE @Pos > 0
BEGIN
SET @Input = STUFF(@Input,@pos,1,”)
SET @Pos = PATINDEX(‘%[^a-z^0-9]%’,@Input)
END
RETURN @Input
END

2. Run the query as:

select * from product
where (dbo.RemoveChars(NAME)) like ‘%’ + (dbo.RemoveChars(‘abcde’))+’%’

 

Reff: http://social.msdn.microsoft.com/Forums/is/transactsql/thread/b5c9f429-1a43-4873-9e3b-b351e45df766

Advertisements
By simplemsexchange Posted in SQL 2008

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s