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.
| Author |
Topic |
|
silentbob
Starting Member
18 Posts |
 Posted - 2012-05-18 : 09:35:51
|
| HiI have a large line of characters within one field in a sql table. THere are about 61k rows. I have a substring select statement that picks the first 6 characters and I need to perform an update/insert into statement that puts the six characters into a new column for each row.First time poster can you help? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-05-18 : 09:37:30
|
[code]update tset new_column = left(a_large_char_column, 6)from a_sql_table t[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
silentbob
Starting Member
18 Posts |
Posted - 2012-05-18 : 09:43:53
|
| that looks good thank you, how would i combine set statements as I need to split up the data in column 4 into numerous pieces. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-05-18 : 09:58:43
|
[code]update tset new_column = left(a_large_char_column, 6), another_col = substring( . . .)from a_sql_table t[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
silentbob
Starting Member
18 Posts |
Posted - 2012-05-18 : 10:15:37
|
| Thanks again, a problem I have noticed which I hope you can help with?in one of the statements I have 'test11 = substring(test4, 30, 15)' which starts at the 30 character and then gets the next 15. Some of the data fills the entire 15 characters and some are just 4 characters at the end of the 15. How do I get sql to omit the spaces? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-05-18 : 10:17:32
|
use rtrim()
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
silentbob
Starting Member
18 Posts |
Posted - 2012-05-18 : 10:18:22
|
| where do I use that, I am just a n00b at the moment |
|
|
|
silentbob
Starting Member
18 Posts |
Posted - 2012-05-18 : 10:31:31
|
| sorted, I used LTrim instead test11 = ltrim(substring(test4, 30, 15)) |
|
|
|
|
|
|
|
|
insert a substring back into the table