SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 help trim or len
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lemondash
Posting Yak Master

United Kingdom
159 Posts

Posted - 01/29/2007 :  08:29:05  Show Profile  Click to see lemondash's MSN Messenger address  Reply with Quote
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

Sweden
30250 Posts

Posted - 01/29/2007 :  08:32:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
ALTER TABLE <YourTableNameHere> ADD <YourNewColumnNameHere> VARCHAR(12)

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

ALTER TABLE <YourTableNameHere> DROP COLUMN <YourOldColumnNameHere>


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

lemondash
Posting Yak Master

United Kingdom
159 Posts

Posted - 01/31/2007 :  10:33:33  Show Profile  Click to see lemondash's MSN Messenger address  Reply with Quote
What about if i wanted to delete the 12 characters from the sting.



I don't like !!!
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 01/31/2007 :  10:36:21  Show Profile  Reply with Quote
update table
set col = left(col, 11)


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 01/31/2007 :  10:36:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

lemondash
Posting Yak Master

United Kingdom
159 Posts

Posted - 01/31/2007 :  10:41:00  Show Profile  Click to see lemondash's MSN Messenger address  Reply with Quote
Khtan.

That command delete everything apart from the 12 characters.

I don't like !!!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 01/31/2007 :  10:42:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
UPDATE <YourTableNameHere>
SET <YourOldColumnNameHere> = SUBSTRING(<YourOldColumnNameHere>, 13, 8000)


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 01/31/2007 :  10:45:56  Show Profile  Reply with Quote
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, '')


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 01/31/2007 :  10:47:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Shouldn't that be

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

???


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 01/31/2007 :  10:51:44  Show Profile  Reply with Quote
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 !!!




KH

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000