| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2014-02-26 : 10:07:09
|
| I have a column ([Device Name]) in a table (Import2262014). I need to remove all data from each row in that column after, and including the first space. So that a row that now says "PENS164014 vm" will then say only "PENS164014".Am i looking at an RTRIM and what would the syntax be? |
|
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-02-26 : 10:43:15
|
| RTRIM removes trailing spaces.You need to use LEFT.SELECT LEFT(Device_name,10) AS Device_new_name FROM Import2262014;!_(M)_! |
 |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2014-02-26 : 10:51:30
|
| That solution is hacking up the name i want created. What is the "10" for? The length of each row varies, so i'm assuming i need a charindex referencing the ' ' that exists in each row? |
|
|
|
kennejd
Starting Member
11 Posts |
Posted - 2014-02-26 : 10:57:57
|
| Try the charindex function:select left(device_name,charindex(' ',device_name)-1) as device_new_name from import.... |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2014-02-26 : 11:06:24
|
| the code i'm running is identical to that:[code]UPDATE import2262014SET servername = LEFT([Device Name], CHARINDEX(' ', [Device Name]) - 1)/[code]it includes the proper column names for my table.but i'm getting the following error:Msg 536, Level 16, State 5, Line 1Invalid length parameter passed to the SUBSTRING function. |
|
|
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-02-26 : 11:14:55
|
| It seems the issue is with SUBSTRING part used in your query. Is this the only query or is it just a part of your query?!_(M)_! |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2014-02-26 : 11:15:33
|
| that's the whole query |
|
|
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-02-26 : 11:17:27
|
| okay....In one of your [Device Name] rows, the '' (blank space) does not exist. So it is generating a negative value for the length part of the LEFT function.That is why it is showing up this error.!_(M)_! |
|
|
|
kennejd
Starting Member
11 Posts |
Posted - 2014-02-26 : 11:18:16
|
| Based on the error message, I'd say there are some device name values that don't have a space (' '). To verify, you should run a query like: select [device name], charindex(' ',[device name]) from import...see if there are device names without the space.If there are, you will have to use a case statement in your update statement. I would use a select statement first to make sure your case is working ok. |
|
|
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-02-26 : 11:25:21
|
| If you want to use the full device name in case of no spaces, you can do as below:SELECT LEFT([Device Name],ISNULL(NULLIF(CHARINDEX('',[Device Name])-1,-1),LENGTH[Device Name])) FROM Import....!_(M)_! |
|
|
|
|
RTRIM?