Simple Search for a Simple Guy 1

[dalec]

I've searched the last 3 nights (lucky me huh!) and I've tested and tested, but, can't get the results I need. Please HELP!!!

What I want to do is search a text field nvarchar(50) from left to right. So, if a user is searching for let's say Targets but only enteres tar this is the result I want:

Tar
Targets
Tarpets
Tarpoops
etc.

I've tried left, substring, like, etc. sometimes I get only the items that begin with "T" (like the above example) except I'll be searching "Tar" and get "tolerance" but that doesnt have Tar in it???

Thanks in advance, please tell me I'm not crazy!

Here's my stored procedure:

@NameSch nvarchar,
@LengthSch integer

AS
SELECT * from supplier_loc
where left(Name,@LengthSch) = @NameSch

order by Name

GO

(Note: the where clause has been changed quite a bit to different things I've been trying, this is just the current version I've been using before posting this).

 

[markros]

I just answered similar question on another forum

Try

declare @NameSearchEnd varchar(30) -- use the len of Supplier_loc

set @NameSearchEnd = @NameSearch + replicate('z',30-LEN(@NameSearch))

select * from myTable where supplier_loc >=@NameSearch and Supplier_Loc< = @NameSearchEnd

 

[dalec]

Ok,

Here's what I changed it to:

	@NameSch nvarchar,
        @LengthSch integer


AS      
        declare @NameSearchEnd nvarchar(50)
        set @NameSearchEnd = @NameSch + replicate('z',50-len(@NameSch))

SELECT * from supplier_loc 

where Name >= @NameSch and Name <= @NameSearchEnd 

order by Name

GO

My results were similar to before:

(Searching TR)

TAD
Tauros auto glass
Tech Plan
Texas Air System
Trane <--------- Really want it to begin list here
Trane Equipment

I think it's closer (thanks, in advance).

 

[markros]

Not sure what exactly is wrong, what is the type of your Name field?

I just tried

declare @test table (ID int identity, myValue varchar(max), dt datetime)

INSERT INTO @Test (MyValue, dt) 
VALUES 
('aaaaaaaa,bbbbbb',getdate()),
('address,d,dd', GETDATE()),
('address 3,dd', getdate()-15), 
 ('bg,fg,fg', getdate()-10),
 ('bgf,gfg', getdate()-15), 
 ('trtrtr,..', getdate()-15) 

SELECT *,convert(char(8), dt, 108) as mytime 
FROM   @Test where myValue >= 'ad' and myValue < 'adrzzzzzzzzzzzzzzzzzzzzzzzzz'

 

[markros]

declare @test table (ID int identity, myValue nvarchar(max), dt datetime)

INSERT INTO @Test (MyValue, dt) 
VALUES 
('aaaaaaaa,bbbbbb',getdate()),
('address,d,dd', GETDATE()),
('Target 3,dd', getdate()-15), 
 ('Target 4,fff', getdate()-10),
 ('Tsdsdsd,sss', getdate()),
 ('bgf,gfg', getdate()-15), 
 ('trtrtr,..', getdate()-15) 
declare @Srch nvarchar(50), @SrchEnd  nvarchar(50)
set @Srch = 'Tar'
set @SrchEnd = @Srch + replicate('z',50-len(@Srch))

SELECT *,convert(char(8), dt, 108) as mytime 
FROM   @Test where myValue >=@Srch  and myValue <= @SrchEnd

 

[danvlas]

Have you tried charindex?

Where charindex(@NameSearch,YourField) = 1

I'd change the name of the column (Name is a reserved word in most languages and could cause you troubles)


Daniel Vlas
Systems Consultant

 

[dalec]

I bracketed the Name field. The field type is nvarchar(50). The charindex function didnt return anything. Doesnt this sound simple for the big deal it has become.

 

[markros]

It sounds like something is wrong either with the data or with the parameter you're passing.

Try

select * from myTable where myField like 'tar%'

If your SQL Server case-insensitive?

 

[dalec]

It's been a week since I've been to get back at this, but, I'm still not having any luck, but, I'm beginning to think it has something to do with the passing varible. It seems to only see the first letter?? I was passing this as a string, but, now I'm fooling around with sqltypes and converting it. If I try this by hand it works correctly, just the stored procedure is killing me.

 

[danvlas]

You have found your answer

@namesch as nvarchar(100) will probably do

Hth


Daniel Vlas
Systems Consultant

 

[dalec]

I know we're close, but, that still did the same thing (gave me all names starting with 't'). I did notice when I decrease the nvarchar(50) that it changes things (like with no number it doesnt display anything) but changing it to 100 didnt change the results from being at 50.

 

[dalec]

I don't think it's seeing anything after the 'and' statement.

 

[dalec]

OK, I think I might have found a key to my problem, it's definiatly in the varibles I'm passing (now I don't know what to change that to) When I hard code the stored procedure with >= 'tr' and <= 'trz' I get the desired results. Now what is wrong with passing a string in the argument and the @NameSch in my stored procedure that could be messing this up?

Thanks in Advance!!!

 

[markros]

Can you please show the beginning of your SP (how did you define your parameter) and the code how you're calling this SP from the application.

 

[danvlas]

Could you also set @LengthSch to a number greater than 1?




Daniel Vlas
Systems Consultant

 

[SQLSister]

Are you failing to specify the size of the variable in the declaration? This will amke it revert to a 1 character variable.

"NOTHING is more important in a database than integrity." ESquared

 

[dalec]

I'm actually not using the @LengthSch anymore here's the current version of the sp:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER  PROCEDURE SupplierSch 

	@NameSch nvarchar(50),
        @LengthSch integer


AS      
        declare @NameSearchEnd nvarchar(50)
        set @NameSearchEnd = rtrim(@NameSch) + 'z'

SELECT * from supplier_loc 

where [Name] >= rtrim(@NameSch) and [Name] <= rtrim(@NameSearchEnd)

order by Name

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

and here is my calling statement:

        Dim Search As String
        Dim LengthSearch As Integer
        Search = Trim(Me.Tag)
        LengthSearch = Len(Me.Tag)
        Me.Supplier_LocTableAdapter.SchSupplierName(Me.ChapmanloadDataSet_11.Supplier_Loc, Search, LengthSearch)

Like I said I've entering the search by hand using the SQL query anayliser and it works, just not in the sp.

Thanks in advance! Dale

 

[markros]

Do you have an access to SQL Profiler to see the exact command you're sending to SQL Server?

 

[dalec]

I might be mis-understanding, but, I'm not using access at all. Is it a tool I can see what is going to the sp?

 

[markros]

Open SSMS (unless you're using Express version of SQL Server) and go to the tools menu. You should find SQL Profiler there. Run it, then run your application and watch in the trace what you're sending to SQL Server.

Couple of links on the topic

http://msdn.microsoft.com/en-us/library/ms979207.aspx

http://vyaskn.tripod.com/analyzing_profiler_output.htm

http://weblogs.sqlteam.com/tarad/archive/2008/08/06/SQL-Profiler-best-practices.aspx