Need a Search Query - SQL Server Forums

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 2005 Forums

Other SQL Server Topics (2005)

Need a Search Query

Author

Topic

mdamjad
Starting Member

1 Post

Posted - 2008-09-25 : 06:05:50

I need a query that has to search for all the possibilities.
Say for eg:I will enter 48448 and I will search for all the possible skus in the tables. I need all the possible records that has %48448% and the values like 4-8448, 48-448, 484-48, 4/8448, 48/448, ..... etc

I cannot use '_' because if i use then it must have values in between the character. So there may/may not a values present in between each character

I need an immediate help on this. Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 06:11:15

will it be always /,- characters in between or can you have any types of characters?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 06:39:51

[code]DECLARE @Search VARCHAR(200)

SET @Search = '48448'

-- Do the right thing
DECLARE @Index SMALLINT

SET @Index = DATALENGTH(@Search)

WHILE @Index > 0
SELECT @Search = STUFF(@Search, @Index, 0, '%'),
@Index = @Index - 1

-- Search
SELECT *
FROM Table1
WHERE Col1 LIKE @Search + '%'[/code]

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-26 : 05:06:19

quote:

Originally posted by Peso

DECLARE	@Search VARCHAR(200)

SET	@Search = '48448'

-- Do the right thing
DECLARE	@Index SMALLINT

SET	@Index = DATALENGTH(@Search)

WHILE @Index > 0
	SELECT	@Search = STUFF(@Search, @Index, 0, '%'),
		@Index = @Index - 1

-- Search
SELECT	*
FROM	Table1
WHERE	Col1 LIKE @Search + '%'

E 12°55'05.63"
N 56°04'39.26"

Very nice. I thought of extracting only numbers from a column and compare. But it would be less effecient than yours

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 05:10:02

Thank you.
I doubt the search will perform fast due to the amount of wildcards, but the code is short and efficient.

E 12°55'05.63"
N 56°04'39.26"

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-09-28 : 11:43:45

My thoughts exactly. If the poster is really faced with this many options in how the data is stored then it is worth somehow storing and indexing a copy with no punctuation, but the requirement for a leading % makes that less useful.
Is this really a requirement?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources