help trim or len - 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 trim or len

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2007-01-29 : 08:29:05

Guys/Girls

Have a small problem I have received a load of data in a flat file format and each column has dividend up with a comma. I have stuck the whole row in to its own column.

What I want to do is take the first 12 characters of the row and put them in there own column.

I think I need to use the Len function or the trim function, so can some body point me in the right direction on how to do this.

Regards Lee

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 08:32:34

ALTER TABLE <YourTableNameHere> ADD <YourNewColumnNameHere> VARCHAR(12)

UPDATE <YourTableNameHere>
SET <YourNewColumnNameHere> = LEFT(<YourOldColumnNameHere>, 12)

ALTER TABLE <YourTableNameHere> DROP COLUMN <YourOldColumnNameHere>

Peter Larsson
Helsingborg, Sweden

lemondash
Posting Yak Master

159 Posts

Posted - 2007-01-31 : 10:33:33

What about if i wanted to delete the 12 characters from the sting.

I don't like !!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 10:36:21

update table
set col = left(col, 11)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 10:36:50

ALTER TABLE <YourTableNameHere> ADD <YourNewColumnNameHere> VARCHAR(12)

UPDATE <YourTableNameHere>
SET <YourNewColumnNameHere> = SUBSTRING(<YourOldColumnNameHere>, 13, 8000)

ALTER TABLE <YourTableNameHere> DROP COLUMN <YourOldColumnNameHere>

Peter Larsson
Helsingborg, Sweden

lemondash
Posting Yak Master

159 Posts

Posted - 2007-01-31 : 10:41:00

Khtan.

That command delete everything apart from the 12 characters.

I don't like !!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 10:42:40

UPDATE <YourTableNameHere>
SET <YourOldColumnNameHere> = SUBSTRING(<YourOldColumnNameHere>, 13, 8000)

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 10:45:56

quote:
Originally posted by lemondash

Khtan.

That command delete everything apart from the 12 characters.

I don't like !!!

sorry i did not read your requirement correctly.

-- this will remove the 12th char
set col = stuff(col, 12, 1, '')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 10:47:37

Shouldn't that be

SET Col = STUFF(Col, 1, 12, '')

???

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 10:51:44

quote:
Originally posted by Peso

Shouldn't that be

SET Col = STUFF(Col, 1, 12, '')

???

Peter Larsson
Helsingborg, Sweden

I tought just the 12th char ?

quote:
Originally posted by lemondash

What about if i wanted to delete the 12 characters from the sting.

I don't like !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources