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
 looking for a space

Author  Topic 

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-09-27 : 17:21:38
Okay, so i've been working in foxpro for a while, but now we're moving on to bigger stronger faster. my question is, in foxpro, i used to be able to fix some name formatting issues with two commands:


replace all fname with subs(name,1,at(' ',name))
replace all lname with subs(name,(at(' ',name)+1),(30-at(' ',name)))


this basically locates the space in the name on each record, and replaces the first name with 0-space and the last with space-end of field

little cude, but it gets the job done. so basically i'm looking for the same solution but for t-sql, i tried something like:

update t1
set name=substring(name,1,at(' ',name))
from temp t1


with no luck, AT() is not an expression in sql, so what is out there that's like it?

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-09-27 : 17:26:47
Didnt get you.. can you pose some sample data and how you want it to look after the update?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-09-27 : 17:35:38
say my name for example, i have some dirty data that in the table it's like this:

|Fname |Lname |Name |
| | |Albert Kohl |


but i want to correct it to:

|Fname |Lname |Name |
|Albert |Kohl |Albert Kohl |


follow me?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-09-27 : 17:36:52
Could you post your data with code tags around it to retain formatting rather than what you used?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-09-27 : 17:42:10
[code]
|Fname |Lname |Name |
| | |Albert Kohl |

but i want to correct it to:

|Fname |Lname |Name |
|Albert |Kohl |Albert Kohl |
[/code]


guess i should have previewed :o(
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-09-27 : 17:43:19
Would the name always have only 2 words or can there be names like "Albert Kohl Jr." or "Albert Kohl III"?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-09-27 : 17:45:24
could have suffixes, but for the most part, it's 2 words. honestly, if i can just get the albert part into the fname and the rest into lname, it would work for now. but i'm open to more elaborate parsing
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-09-27 : 17:48:47
if i got the sytex to just locate that space, i can build off of that and come up with the rest. (like testing to see if it's formatted as fname middle initial lname, or if there are prefix/suffixes, etc)
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-09-27 : 17:52:44
You can use the function from here: http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx
but you might have to tweak it a little bit.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-09-27 : 18:07:06
okay, but looks like i pass it the text, how would i pass it a whole table?
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2007-09-27 : 18:23:39
Here is one super simple way: 
DECLARE @Table TABLE (Fname VARCHAR(50), Lname VARCHAR(50), Name VARCHAR(100))

INSERT @Table
SELECT '', '', 'Albert Kohl'

UPDATE
	@Table
SET
	FName = LEFT(Name, CHARINDEX(' ', Name) - 1),
	LName = RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name))

SELECT *
FROM @Table
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-09-27 : 18:47:19
Okay, so if i use this exact code, it works perfectly, so i tweaked it a little:

--DECLARE @Table tempdb (Fname VARCHAR(50), Lname VARCHAR(50), Name VARCHAR(100))

--INSERT @Table
--SELECT '', '', 'Albert Kohl'

UPDATE
	tempdb
SET
	FName = LEFT(Name, CHARINDEX(' ', Name) - 1),
	LName = RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name))

FROM tempdb



but i get the following error:
Server: Msg 536, Level 16, State 3, Line 6
Invalid length parameter passed to the substring function.
The statement has been terminated.


i also tried turning back on the declare line, and got this instead:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Fname'.
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-09-27 : 18:57:10
[code]
UPDATE
tempdb
SET
FName = LEFT(Name, CHARINDEX(' ', Name) - 1),
LName = RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name))
FROM tempdb
[/code]


Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-09-27 : 18:58:42
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.



:o(
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-09-27 : 19:02:11
Obviously there is some data that is conflicting with the Update. If you look at the data do you see any particular rows of data that stand out, that are not the regular 'fname lastname' type?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-09-27 : 19:09:14
couple examples:

Berkom R Van
Bernal
Bhattacharya
Blake
Blawn
Bocholin
Bonaventure Tan


as for the first,middle,last, as i said, it's cool for it to do something like: |albert w kohl| converts to: |albert|w kohl|
is that's what maybe throughing it off?
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-09-27 : 19:20:18
if i just run: 


UPDATE
	tempdb
SET
		LName = RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name))



it does that fine, and i checked it and it's cool

i am noticing that some of the first names are just a first letter, like |A Kohl| would that mess it up too?
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-09-27 : 19:28:18
YESSS i just changed it to:


UPDATE
	tempdb
SET
        FName = rtrim(LEFT(Name, CHARINDEX(' ', Name)))
        LName = RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name))



and it did the job. tx alot!!!!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-28 : 02:44:39
[code]UDPATE Table1
SET firstName = dbo.fnParseString(-1, ' ', fullName),
lastName = dbo.fnParseString(-2, ' ', fullName)

UDPATE Table1
SET firstName = dbo.fnParseString(-1, ' ', fullName),
lastName = dbo.fnParseString(1, ' ', fullName)[/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site