jpetter
asked on
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.N
from tblUs1Test, tblSupported
where ((charindex (upper (@fname), upper (tblUs1Test.UserName)) > 0 ) and
(charindex (upper (@lname), upper (tblUs1Test.UserName)) > 0 ))
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.N
from tblUs1Test, tblSupported
where ((charindex (upper (@fname), upper (tblUs1Test.UserName)) > 0 ) and
(charindex (upper (@lname), upper (tblUs1Test.UserName)) > 0 ))
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,chari
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
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,chari
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
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,chari
charindex (' ', t.UserName) - charindex('.', t.UserName) -1 ) as LastName
from tblUs1Test t
) t1 on t1.FirstName = s1.FirstName and t1.LastName = s1.LastName
Sash
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,chari
charindex (' ', t.UserName) - charindex('.', t.UserName) -1 ) as LastName
from tblUs1Test t
) t1 on t1.FirstName = s1.FirstName and t1.LastName = s1.LastName
Sash
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Your welcome.
Glad to be of some assistance.
Glad to be of some assistance.
ASKER
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,chari
charindex (' ', t.UserName) - charindex('.', t.UserName) -1 ) as LastName
from tblUs1Test t
where t.UserName is not null
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,chari
charindex (' ', t.UserName) - charindex('.', t.UserName) -1 ) as LastName
from tblUs1Test t
where t.UserName is not null
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
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
ASKER
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
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
I am working on a solution that only parses when data fits the required pattern, I will get back to you soon.
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,chari
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)
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,chari
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)
ASKER
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
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
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.
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.
ASKER
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
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
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.
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.
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*