Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

How to Write a Query That Uses Variables

I have a problem that I'm trying to solve using SQL, and though I'm pretty sure it can be done, my query writing skills are pretty remedial, which makes it tough for me. And I've been reading code samples and documentation all day, to no avail.

Here is the situation. I need to extract employee's company IDs from a field in one table, where there is a matching name from another table. Sounds easy, but there is not a consistent format for the names in the table containing the IDs, and also in that table the ID has been appended to the name field following a hyphen.

So, what I have to deal with is this: (example field value from each table)
- Need to get IDs for all users in this table
  - tblSupported.NAME_FULL -- sample data = RIGBY, LOIS

- This is the table I need to get them from
  - tblUs1Test.UserName -- sample date = Lois.Rigby - G221077

My game plan was to strip out the names from the first table mentioned using substring and charindex functions, and store the contents in variables, and then use charindex to see if the contents of the variables existed in the second table. Once I get the select statement working, I'll either append the ID to the first table, or make it a select into statement and create a new table with the names and IDs. The code I came up with is listed below, but the Query Analyzer definitely does not like my syntax.

If anyone has any ideas, I would appreciate them very much.

Thanks,
Jeff

declare      @fname varchar(30), @lname varchar (30)
select      @lname = substring (tblSupported.NAME_FULL, 1, charindex (',', tblSupported.NAME_FULL)
select      @fname = substring (tblSupported.NAME_FULL, charindex (',', tblSupported.NAME_FULL) + 1,
                  len (tblSupported.NAME_FULL) - charindex (',', tblSupported.NAME_FULL) + 1
select      /*userID*/substring (tblUs1Test.UserName, charindex ('-', tblUs1Test.UserName) + 1,
      len(tblUs1Test.UserName)- charindex ('-', tblUs1Test.UserName) + 1) as UserID,
      /*UserName*/tblSupported.NAME_FULL as UserName
from      tblUs1Test, tblSupported
where      ((charindex (upper (@fname), upper (tblUs1Test.UserName)) > 0 ) and
      (charindex (upper (@lname), upper (tblUs1Test.UserName)) > 0 ))

0
jpetter
Asked:
jpetter
  • 9
  • 5
1 Solution
 
svidCommented:
First Off

select     @lname = substring (tblSupported.NAME_FULL, 1, charindex (',', tblSupported.NAME_FULL)

select     @fname = substring (tblSupported.NAME_FULL, charindex (',', tblSupported.NAME_FULL) + 1,
               len (tblSupported.NAME_FULL) - charindex (',', tblSupported.NAME_FULL) + 1

cannot be done.

The correct syntax is
select @lname = substring(*whatever*) from table where columnname = *some value*
0
 
SashPCommented:
Hi jpetter

Try this

SELECT
    *
FROM
    (
    select
        substring (s.NAME_FULL, 1, charindex (',',s.NAME_FULL) LastName,
        substring (s.NAME_FULL, charindex (',', s.NAME_FULL) + 1,
                   len (s.NAME_FULL) - charindex (',', s.NAME_FULL) + 1 ) FirstName
    FROM tblSupported s
    ) s1
JOIN
    (
    select    
        substring (t.UserName, charindex ('-', t.UserName) + 2,
             len(t.UserName)- charindex ('-', t.UserName) + 1) as UserID,
        LEFT(t.UserName,charindex ('.', t.UserName) - 1) as FirstName,
        substring(t.UserName,charindex('.', t.UserName) + 1,
        charindex (' ', t.UserName) - charindex('.', t.UserName) -1 ) as LastName,
    from     tblUs1Test t
    ) t1 on t1.FirstName = s1.FirstName and t1.LastName = s1.LastName


Cheers Sash
0
 
SashPCommented:
Sorry jpetter there were a couple of syntax errors in the query

Try

SELECT
    *
FROM
    (

select
        substring (s.NAME_FULL, 1, charindex (',',s.NAME_FULL)-1) LastName,
        substring (s.NAME_FULL, charindex (',', s.NAME_FULL) + 2,
                   len (s.NAME_FULL) - charindex (',', s.NAME_FULL) + 1 ) FirstName
    FROM tblSupported s
    ) s1
JOIN
    (
    select    
        substring (t.UserName, charindex ('-', t.UserName) + 2,
             len(t.UserName)- charindex ('-', t.UserName) + 1) as UserID,
        LEFT(t.UserName,charindex ('.', t.UserName) - 1) as FirstName,
        substring(t.UserName,charindex('.', t.UserName) + 1,
        charindex (' ', t.UserName) - charindex('.', t.UserName) -1 ) as LastName
    from     tblUs1Test t
    ) t1 on t1.FirstName = s1.FirstName and t1.LastName = s1.LastName

Sash
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
SashPCommented:
The approach is to take the two tables and produce a format that we can handle.

So we must seperate the data fields from each of the columns.

First the tblSupported table.

Have a look at the output of

select
     substring (s.NAME_FULL, 1, charindex (',',s.NAME_FULL)-1) LastName,
     substring (s.NAME_FULL, charindex (',', s.NAME_FULL) + 2,
                len (s.NAME_FULL) - charindex (',', s.NAME_FULL) + 1 ) FirstName
FROM tblSupported s

Then the tblUs1Test  table

Have a look at the output of

    select    
        substring (t.UserName, charindex ('-', t.UserName) + 2,
             len(t.UserName)- charindex ('-', t.UserName) + 1) as UserID,
        LEFT(t.UserName,charindex ('.', t.UserName) - 1) as FirstName,
        substring(t.UserName,charindex('.', t.UserName) + 1,
        charindex (' ', t.UserName) - charindex('.', t.UserName) -1 ) as LastName
    from     tblUs1Test t

Then it is a simple matter of joining the two derived tables

SELECT
    *
FROM
    (
    select
        substring (s.NAME_FULL, 1, charindex (',',s.NAME_FULL)-1) LastName,
        substring (s.NAME_FULL, charindex (',', s.NAME_FULL) + 2,
                   len (s.NAME_FULL) - charindex (',', s.NAME_FULL) + 1 ) FirstName
    FROM tblSupported s
    ) s1
JOIN
    (
    select    
        substring (t.UserName, charindex ('-', t.UserName) + 2,
             len(t.UserName)- charindex ('-', t.UserName) + 1) as UserID,
        LEFT(t.UserName,charindex ('.', t.UserName) - 1) as FirstName,
        substring(t.UserName,charindex('.', t.UserName) + 1,
        charindex (' ', t.UserName) - charindex('.', t.UserName) -1 ) as LastName
    from     tblUs1Test t
    ) t1 on t1.FirstName = s1.FirstName and t1.LastName = s1.LastName

I like to use a format where indentation and carriage returns are used to help me see where derived tables start and finish.

Alternatively you could replace the derived tables with views

create view dbo.vw_tblSupported_Cleaned
as
select
     substring (s.NAME_FULL, 1, charindex (',',s.NAME_FULL)-1) LastName,
     substring (s.NAME_FULL, charindex (',', s.NAME_FULL) + 2,
                len (s.NAME_FULL) - charindex (',', s.NAME_FULL) + 1 ) FirstName
FROM tblSupported s


and

create view dbo.vw_tblUs1Test _Cleaned
as
    select    
        substring (t.UserName, charindex ('-', t.UserName) + 2,
             len(t.UserName)- charindex ('-', t.UserName) + 1) as UserID,
        LEFT(t.UserName,charindex ('.', t.UserName) - 1) as FirstName,
        substring(t.UserName,charindex('.', t.UserName) + 1,
        charindex (' ', t.UserName) - charindex('.', t.UserName) -1 ) as LastName
    from     tblUs1Test t


then the query would look much simpler

SELECT
    *
FROM vw_tblSupported_Cleaned s
JOIN vw_tblUs1Test_Cleaned t on t.FirstName = s.FirstName and t.LastName = s.LastName

Sash

0
 
jpetterAuthor Commented:
Sash,

Thanks so much for providing such a detailed solution and explanation. I'm going to go through this again, so I can understand exactly what you did and then give it a try.

Thanks again,
Jeff
0
 
SashPCommented:
Your welcome.

Glad to be of some assistance.
0
 
jpetterAuthor Commented:
Sash,

I'll probably close this out and awrd you the points very shortly, as I really do appreciate the in depth help that you've provided. I having a problem though trying to get one of the select statements to work. The syntax checks out okay, but when I try to run it, I receive:
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

I thought that maybe it was because some of the records have a null for that value, so I put a qualifying condition in the where clause, but it didn't help. I also tried using 'datalength' rather than 'len', but that didn't help either. The problem piece if right here, and if anything jumps out at you, I would appreciate it if you could let me know. Otherwise, I'll continue to play with it, and will award the points regardless.

Thanks,
Jeff

select    
      substring (t.UserName, charindex ('-', t.UserName) + 2, len(t.UserName) - charindex ('-', t.UserName) - 1)    
                         as UserID,
                LEFT(t.UserName,charindex ('.', t.UserName) - 1) as FirstName,
                substring(t.UserName,charindex('.', t.UserName) + 1,
                                       charindex (' ', t.UserName) - charindex('.', t.UserName) -1 ) as LastName
from          tblUs1Test t
where      t.UserName is not null
0
 
SashPCommented:
It sounds like you are on the right track.

I think that the problem may be that the data may not be in the format that we are expecting.  The substring functions that we use make the assumption that the data is of the format

Lois.Rigby - G221077

Consider what happens if the user's last name is hyphenated or the first has a space in it.

The charindex (' ', t.UserName) - charindex('.', t.UserName) will be negative if the firstname has a space in it.

Some though is going to be required to get this working, it is a string parsing problem now I believe rather than a pure SQL problem.

If you can identify the records which are causing the problem it will go a long way to solving the problem.

Post the offending records and I will help you work through it.

Cheers Sash




0
 
jpetterAuthor Commented:
Sash,

Once again, thanks!

That certainly does appear to be it. There are a number of records that do not fit the expected convention. A few of the non-standard records are:
administrator
Amar Gupta
Aas_smwz
 Al.T.Meadows - Z703211 (space prior to the first character)

I'm going to expand my where condition clause to only include those records that contain a hyphen, and where the first position in the string is not a space.

I'll let you know how it works out.

Thanks,
Jeff
0
 
SashPCommented:
I am working on a solution that only parses when data fits the required pattern, I will get back to you soon.
0
 
SashPCommented:
Try this

declare @tblUs1Test table (UserName varchar(100))

insert into @tblUs1Test values('Lois.Rigby - G221077')
insert into @tblUs1Test values('Mary Sue.Rigby - G221077')
insert into @tblUs1Test values('administrator')
insert into @tblUs1Test values('Amar Gupta')
insert into @tblUs1Test values('Aas_smwz')
insert into @tblUs1Test values(' Al.T.Meadows - Z703211')

select    

  right( t.UserName , charindex( ' - ' , reverse(t.UserName) ) - 1 )  UserId,

  LEFT(t.UserName,charindex ('.', t.UserName) - 1)  FirstName,

  substring(t.UserName,charindex('.', t.UserName) + 1,
  charindex (' ', t.UserName,charindex('.', t.UserName)) - charindex('.', t.UserName) -1 ) as LastName

from          @tblUs1Test t
where    
    t.UserName is not null
    and ((charindex( ' - ' , reverse(t.UserName) )) - 1 > 0 )
    and ((charindex ('.', t.UserName) - 1) > 0)
    and (len(t.UserName) - charindex( ' - ' , reverse(t.UserName))-1) > (charindex ('.', t.UserName)-1)

0
 
jpetterAuthor Commented:
Haha...Awesome! That worked like a charm.

Now, I'll have to look through the SQL docs to see what you did as I notice some new internal functions like 'reverse' I haven't seen before. This SQL is pretty new to me, as I work almost exclusively in C/C++. But it's pretty cool.

I really appreciate your help. I've learned quite a bit from your code and your detailed explanations.

Thanks again,
Jeff

0
 
SashPCommented:
Your welcome jeff

The reverse function reverses the order of a string ie.  'My string' becomes 'gnirts yM'

The reason for doing it is to retrieve the last ' - ' in case one appears in the name.
0
 
jpetterAuthor Commented:
Okay, that makes sense, and hyphens are quite common in names these days.

I think the individual or group responsible for collecting and maintaining data that in my opinion borders on garbage, should be hung, but it does tend to bring some creative code out.

Thanks,
Jeff
0
 
SashPCommented:
A very important rule for "Normalised" data is to remove compound fields.  Fields that contain more than on piece of information.

They are slower to query as indexes cannot be utilised, and using the data is more complex as we have just prooven.  If you can fix the database it may well be worth it.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now