Help with SQL Query Analyzer - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help with SQL Query Analyzer

Author

Topic

kbmccarthy
Starting Member

6 Posts

Posted - 2006-01-17 : 17:21:15

I'm trying to do a couple of things with some different tables.

With the first table, I have first name and last name in two separate columns.

I'm trying to create a new ID column that would do the following.

Take the first 3 letters of the last name (and remove any non characters such as ',&) and the first 3 letters of the first name, and then combine them.

I know how to use substring to take the first three, and I know how to combine them, but I'm having problems removing the non alpha characters.

For example, if the columns looked as follows:
First Last
John O'Connor

I'd want the result to look like OCOJOH, however, I'm getting O'CJOH.

The second issue is how would I do something similar if the First and Last Name were in the same column and string.

For example, if the column looks as follows:

Name
John O'Connor Publishing

I'd like to pull the first 3 from the first string before the space and then the next 3 so it would be as follows:

JOHOCO

Finally, I want to compare these new IDs, and add numbers to them.

So if I had the following
First Last
John O'Connor
Johnny O'Connell

The first would be OCOJOH01
The second would be OCOJOH02

If there are not any duplicates with the new ID, I'd like to call it OCOJOH01 and then move on.

Any help/ideas would be greatly appreciated.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-17 : 21:23:18

Try this

create table #temp
(
	id		int identity(1,1),
	firstname	varchar(50),
	lastname	varchar(50),
	fullname	varchar(50),
	new_id		varchar(10)
)

delete #temp
insert into #temp(firstname, lastname, fullname)
select 'John',   'O''Connor', NULL		         union all
select NULL,     NULL,       'John O''Connor Publishing' union all
select 'Johnny', 'O''Connel', NULL		         union all
select 'Tim',    'Carter',    NULL			 union all
select 'Tim',    'Carpenter', NULL

update 	#temp
	set	new_id = upper(left(replace(firstname, '''', ''), 3)) + upper(left(replace(lastname, '''', ''), 3))
	where	fullname is null

select * from #temp

update	#temp
	set	new_id = upper(left(replace(fullname, '''', ''), 3) + substring(replace(fullname, '''', ''), charindex(' ', replace(fullname, '''', '')) + 1, 3))
	where	fullname is not null

select * from #temp

update	#temp
	set	new_id = new_id + right('0' + convert(varchar(2), (select count(*) from #temp x where x.new_id = t.new_id and x.id <= t.id)), 2)
	from	#temp t

select * from #temp

drop table #temp

-----------------
'KH'

kbmccarthy
Starting Member

6 Posts

Posted - 2006-01-18 : 19:54:39

Thank you very much.

My next question is as follows. What if I'm dealing with 10,000 names?

I don't know which ones have the ' like John O'Connor. Do I have to do a select for each of the 10,000 rows, or can I pull them all in from one database, if I know that the first names are stored in column first and the last names are stored in column last?

It looks like from your example:
select 'John', 'O''Connor', NULL union all

That you manually removed the ' in O'Connor by using SELECT 'O''Connor'.

Is there a way, from an SQL standpoint, that I can pull in the last name from the last column, in one of my tables, and remove the ' from O'Connor without typing it into the select, and can I pull all of my last names into this query.

Thanks again for all of your help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-18 : 20:22:34

quote:
My next question is as follows. What if I'm dealing with 10,000 names?

What is your concern on this ? This should be a one off excercise right ?

quote:
I don't know which ones have the ' like John O'Connor. Do I have to do a select for each of the 10,000 rows, or can I pull them all in from one database, if I know that the first names are stored in column first and the last names are stored in column last?

You don't have to identified which record contain the ' . The sample data that i used contain both type. Those with ' as in "John 'Oconnor" and without as in "Tim Carpenter".

quote:
That you manually removed the ' in O'Connor by using SELECT 'O''Connor'.

I did not manually remove the '. As the single quote is used to identify a string, in order to have the single quote character (') inside a string, i have to escape it by specifying it twice.

Try to run this Select 'O''Connor'

It will return you O'Connor

quote:
Is there a way, from an SQL standpoint, that I can pull in the last name from the last column, in one of my tables, and remove the ' from O'Connor without typing it into the select, and can I pull all of my last names into this query.

Don't quite get what you want. Can elaborate more ?

-----------------
'KH'

kbmccarthy
Starting Member

6 Posts

Posted - 2006-01-18 : 20:28:24

Thanks again for your help and patience.

quote:
quote:Is there a way, from an SQL standpoint, that I can pull in the last name from the last column, in one of my tables, and remove the ' from O'Connor without typing it into the select, and can I pull all of my last names into this query.

Don't quite get what you want. Can elaborate more ?

What I'm wondering is, that in the Select you are using the actual text of a name, can I just pull in all the rows from the first column, or do I need to type them all out?

Let's say my table is called Vendor, and the column named first has all the first names and the column names have all the last names, can the select look like the following:

insert into #temp(firstname, lastname, fullname)
select First, Last, NULL union all
from Vendor

Would that work?

Again, thanks for your help, time and patience.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-18 : 20:42:48

you don't have to use the #temp. I use the #temp because i do not have your actual table. For testing and illlusration purposes, i created a temp table #temp. You can just modify the update command to use your actual table.

-----------------
'KH'

kbmccarthy
Starting Member

6 Posts

Posted - 2006-01-18 : 21:14:54

Excellent.

Thanks a million.

One last thing. How would I update the replace commands to remove periods, ampersands, and other non alpha characters?

Thanks again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-18 : 21:30:07

You can create your own function (UDF) that uses replace() function to remove any unwanted chars

-----------------
'KH'

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-01-19 : 03:21:08

There are some functions here [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54333[/url] and here [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50044[/url] that may also be of use

steve

-----------

Facts are meaningless. You could use facts to prove anything that's even remotely true!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources