0

I have a table in my database for storing supplier contacts and it is laid out like so:

Table: supplier_contacts
    contact_id  INT IDENTITY(1,1)
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NULL,
    supplier_id INT

I have an external VB.Net project that will be using the database, and will be passing in contact names as string with the first and last names put together. For example:

Database:
    first_name = John
    last_name = DOe

VB.Net:
    name = "John Doe"

My thought in order to properly get the contact_id value is to just put together a stored procedure that can be called that does a search through the table using substrings and such. My issue is I can't get it to work.

This is basically what I have some up with for a sort of "proof of concept" query to see if I can get this to work.

DECLARE @Contact   VARCHAR(150) --Value for the full contact name
DECLARE @ContactID INT

SELECT @ContactID = contact_id
  FROM supplier_contacts
 WHERE first_name = SUBSTRING(@Contact, 1, CHARINDEX(' ', @Contact) - 1) AND
       last_name = SUBSTRING(@Contact, CHARINDEX(' ', @Contact) + 1, LEN(@Contact))

My problem comes when the last_name column is blank, since it isn't a mandatory field. So I am not sure how to set up the last_name condition to essentially be an optional check.

Improve this question

2 Answers 2

Reset to default
2

How about something like this..

DECLARE @Contact   VARCHAR(150) --Value for the full contact name
DECLARE @ContactID INT

SELECT @ContactID = contact_id
  FROM supplier_contacts
 WHERE (first_name = SUBSTRING(@Contact, 1, CHARINDEX(' ', @Contact) - 1) AND
       last_name = SUBSTRING(@Contact, CHARINDEX(' ', @Contact) + 1, LEN(@Contact)))
or
first_name = SUBSTRING(@Contact, 1, CHARINDEX(' ', @Contact) - 1)
Improve this answer
2
  • I think I misunderstood.. the last name column in the db is optional. The first part of the answer should satisfy the query requirement, but searching only the first name could potentially return more than one contact id causing the query to fail.
    – Goforebroke
    Commented Jul 23, 2018 at 17:34
  • yeah, I realized the last part after reading your reply. I added in a final WHERE condition outside of the two OR conditions that checks to make sure it is the correct supplier. Thanks for the help.
    – Skitzafreak
    Commented Jul 23, 2018 at 17:40
1

This should solve your problem:

DECLARE @Contact   VARCHAR(150) --Value for the full contact name
DECLARE @ContactID INT

SELECT TOP 1 @ContactID = contact_id
  FROM supplier_contacts
 WHERE TRIM(UPPER(first_name +' '+ isnull(last_name,''))) = TRIM(UPPER(@Contact))
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.