Manipulating Data in SQL - 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

Manipulating Data in SQL

Author

Topic

ISBB
Starting Member

12 Posts

Posted - 2009-08-16 : 15:23:15

Afternoon fellas. I hear this is the place for anything and everything sql. Im a sysamdin thrown into a dba spot with no formal training so im trying to feel and figure things out as i go. I usually use a program called csved to manipulate data but with 5 million + records it doesnt quite like it. so inevitably i need this data in sql i figured i would ask if there is a easy way to do it in sql.

I have a table which contains.
Name,address,city,state,zip,telephone.

The 2 things i need to get done is
a) in the name column i need to copy that data into 2 more columns fname and lname. splitting the Name collumn after the first space. As the field is setup as last first.
b) i need to copy the telephone column into another collumn called tl1 w/ the - split. so it would go from 555-555-5555 in telephone to 5551231234 in the tl1 column.

I have read some instances but programming is not my thing but i can usually butcher up a sample and reverse engineer it to work for me.. so if anyone has some quick snippets that are related i can usually get them to work for my needs :D

Thanks again fellas.

Yep.. Point and Click dont work here..

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-16 : 16:18:50

[code]
ALTER TABLE YourTable
Add FirstName varchar(32),
LastName varchar(32),
tl1 char(10)
GO

UPDATE YourTable
SET FirstName = left(name, charindex(' ', name) - 1),
LastName = Right(name, len(name) - charindex(' ', name)),
tl1 = Replace(telephone, '-', '')[/code]

Careful though...What if someone's name has a space in it? Like Jean Luc or something? Also, this script will fail if there is NO space in a name

ISBB
Starting Member

12 Posts

Posted - 2009-08-16 : 16:32:38

when i export the # of records i need my fixed length conversion software puts them back together in a way.. I need the names split up so i can copy the first name to another column down the road.. I will give it a shot.. its only 10million records so im sure its bound to fail lol..

Yep.. Point and Click dont work here..

ISBB
Starting Member

12 Posts

Posted - 2009-08-16 : 16:56:12

hmmmm keep getting a incorect syntax error

"Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'charindex'.
"

Yep.. Point and Click dont work here..

ISBB
Starting Member

12 Posts

Posted - 2009-08-16 : 17:02:19

hmmmmmmmm...

Yep.. Point and Click dont work here..

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-16 : 18:08:24

show me your query.

also, what version of sql server -- what does this return:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

ISBB
Starting Member

12 Posts

Posted - 2009-08-16 : 18:35:22

2008 Express

Yep.. Point and Click dont work here..

ISBB
Starting Member

12 Posts

Posted - 2009-08-16 : 18:37:33

ALTER TABLE idearc
Add fname varchar(32),
lname varchar(32),
tl1 char(10)
GO

UPDATE idearc
SET fname = left(name, charindex(' ', name) - 1),
lname = Right(name, len(name) - charindex(' ', name)),
tl1 = Replace(telephone, '-', '')

Yep.. Point and Click dont work here..

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-16 : 22:16:28

there's no syntax error. hmmm...what if you enclose name in square brackets?


UPDATE idearc
SET    fname = left([name], charindex(' ', [name]) - 1),
       lname = Right([name], len([name]) - charindex(' ', [name])),
       tl1 = Replace(telephone, '-', '')

ISBB
Starting Member

12 Posts

Posted - 2009-08-16 : 22:48:46

Myself and a buddy have refined it a bit using substring instead.. and came across a new error.. im trying to get around Null's since this is where its erroring out. It see's a null field and freaks..

Current query

UPDATE [sample]

SET   fname = substring(name,1,charindex(' ', name)-1),

      lname = substring(name,charindex(' ', name)+1,len(name)-charindex(' ', name)),

      tl1 = Replace(phone, '-', '')

Yep.. Point and Click dont work here..

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-16 : 23:18:38

WHERE name is not null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources