How to implement wild Card search

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

Transact-SQL (2005)

How to implement wild Card search

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-06-04 : 07:18:38

I have a check number which could be alphaneumeric
I have to implement search on them.
FromCheckNumber ToCheckNumber
i.e. I will provide a value From Value and To value in the Text boxes
Result should be in between them
How Can I do That ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 07:23:13

It depends.
What is the datatype for Checknumber?

Peter Larsson
Helsingborg, Sweden

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-06-04 : 07:38:15

NVarchar(50)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 07:50:52

select * from table1
where col1 >= @fromchecknumber and col1 <= @tochecknumber

Peter Larsson
Helsingborg, Sweden

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-06-04 : 08:24:33

Not Working
Data is like as follows
CheckNumber
111
111
111
111
11
11
11
11
11
xxx777888

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 08:31:44

Yes, and what are your FromCheckNumber and ToCheckNumber?

Peter Larsson
Helsingborg, Sweden

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-06-04 : 08:39:27

FromcheckNumber 15
ToCheckNumber 1000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 08:47:23

Yes, and based on those two checknumbers, which records in your provided sample data would you like to return?

Peter Larsson
Helsingborg, Sweden

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-06-04 : 09:03:57

111
111
111
111

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 09:09:57

What about this sample data?

xyz20abc1002ghi?

Peter Larsson
Helsingborg, Sweden

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-06-04 : 09:11:51

this should be seen also there are some Records which have no checknumber that is ''

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 09:25:41

[code]-- Prepare sample data
DECLARE @From VARCHAR(50),
@To VARCHAR(50)

SELECT @From = '15',
@To = '1000'

DECLARE @Sample TABLE (Data VARCHAR(50))

INSERT @Sample
SELECT '111' UNION ALL
SELECT '11' UNION ALL
SELECT 'xxx777888' UNION ALL
SELECT 'xyz20abc1002ghi'

select q.data
from (
select data,
dbo.fnFilterString(data, '[0-9]', '#', 1) as d1 -- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083
from @sample
) as q
cross apply dbo.fnParseList('#', d1) as e -- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033
where cast(e.data as int) >= cast(@from as int)
and cast(e.data as int) <= cast(@to as int)[/code]

Peter Larsson
Helsingborg, Sweden

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-06-04 : 09:28:35

Thanks Peso

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-04 : 11:14:19

Not sure if this works(assuming you are looking for integers)


Where case 
         when data not like '%[^0-9]%' then 
              cast(data as int) 
         else 
             0 
      end 
between 
       cast(@from as int) 
       and cast(@to as int)

Madhivanan

Failing to plan is Planning to fail

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-06-14 : 06:40:34

Problem is that user can provide alphabet in the @from and @to parameters.
Then this thing fails
What Can I do ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-14 : 09:08:09

Have tried suggested methods?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources