Using Wild Cards in procedure variables

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

Transact-SQL (2000)

Using Wild Cards in procedure variables

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2010-03-01 : 12:00:22

We've got a jacked up Employee Table in our SQL 2000 database - An old VB5 application uses a Microsoft Access database. Once a day, a job on the SQL Server is run to pull data from the Access database over to the server. It deletes the existing SQL Employee table and replaces it with the data from Access.

There are no keys in the table, and all of the fields are straight nVarChar(255) fields. The old VB5 application failed to trim leading or trailing spaces from the text fields and does no error checking.

To compound the problem, we have machine records that store the Full Name of the Employee who was operating certain equipment. Matching Full Names to the correct Employee Badge Number has proved difficult.

I've written a stored procedure called "sp_GetBadge(@FullName nVarChar(255))" to handle this problem.

It starts by trimming the spaces from the full name, then looks for the first blank space. If it finds one, it creates a @FirstName variable out of the first part of the string, then starts at the end of the string and again looks for the first space from the rear (for last names like "De La Rosa") to fill the @LastName.

So, given a full name like " Maria Jo De La Rosa " (I'm hoping that is the worst case scenario), I am able to create @FirstName of "Maria" and @LastName of "Rosa".

Now, I need to select where "(FIRSTNAME Like 'Maria%') AND (LASTNAME Like '%Rosa')"; however, every time I try to append the '%' wild card to my search variables and use the 'Like' filter, I get 0 records returned. If I select where "(FIRSTNAME=@FirstName) AND (LASTNAME=@LastName)" (i.e. No Wild Cards - exact match only), all entries with single word first names and last names will display.

Could someone a little more knowledgeable on writing stored procedures take a look at my code to let me know what I may have done wrong?

PROCEDURE [dbo].[sp_GetBadge](@FullName nVarChar(255)) AS BEGIN
  declare @FirstName nVarChar(255), @LastName nVarChar(255)
  declare @space nVarChar(1), @lastChar nVarChar(1)
  declare @index int
  set @space=' ' -- constant - do not change
  set @FirstName='' -- initialize just in case there are no spaces!
  set @LastName=LTrim(RTrim(@FullName))
  set @index=CharIndex(@space, @LastName)
  if (0 < @index) Begin
    set @FirstName=SubString(@LastName, 1, @index)
    set @index=Len(@LastName)-1
    set @lastChar=SubString(@LastName, @index, 1) -- starts at the end...
    while (@lastChar!=@space) begin
      set @index=@index-1 -- ...and walks back one char at a time
      set @lastChar=SubString(@LastName, @index, 1)
    end
    set @LastName=SubString(@LastName, @index+1, Len(@LastName))
  end
  if (@LastName!='') begin
    set @FirstName=@FirstName + '%'
    set @LastName='%' + @LastName
    select top 1 NUM
    from EmployeeTable
    where (FIRSTNAME Like @FirstName) AND (LASTNAME Like @LastName)
    order by [COUNT] desc
  end else begin
    select @LastName As 'NUM'
  end
END

I appreciate anyone's ideas.

~Joe

Avoid Sears Home Improvement

jp2code
Posting Yak Master

175 Posts

Posted - 2010-03-01 : 12:08:37

Never mind. Stop struggling to figure this out. I was off by 1 on my First Name setting:

set @FirstName=SubString(@LastName, 1, @index - 1)

Avoid Sears Home Improvement

venkat09
Starting Member

17 Posts

Posted - 2010-03-02 : 05:13:01

declare @FullName nvarchar(100)
declare @RevName nvarchar(100)
declare @Index int
declare @FirstName nvarchar(100)
declare @LastName nvarchar(100)

set @FullName = 'Maria Bla bla Ringah Rosa'
set @RevName = Reverse(@FullName)

set @Index = CharIndex(' ', @FullName)

if @Index <> -1
set @FirstName = Substring(@FullName, 1, @Index - 1)

set @Index = CharIndex(' ', @RevName)

if @Index <> -1
set @LastName = Reverse(Substring(@RevName, 1, @Index - 1))

You get @FirstName = 'Maria' @LastName = 'Rosa'

You don't need truncate the full name until you find the last part of her name in a loop

Venkat R. Prasad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources