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
 RTRIM?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WJHamel
Aged Yak Warrior

USA
646 Posts

Posted - 02/26/2014 :  10:07:09  Show Profile  Reply with Quote
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

Canada
71 Posts

Posted - 02/26/2014 :  10:43:15  Show Profile  Reply with Quote
RTRIM removes trailing spaces.
You need to use LEFT.
SELECT LEFT(Device_name,10) AS Device_new_name FROM Import2262014;

!_(M)_!
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
646 Posts

Posted - 02/26/2014 :  10:51:30  Show Profile  Reply with Quote
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?
Go to Top of Page

kennejd
Starting Member

USA
11 Posts

Posted - 02/26/2014 :  10:57:57  Show Profile  Reply with Quote
Try the charindex function:

select
left(device_name,charindex(' ',device_name)-1) as device_new_name from import....
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
646 Posts

Posted - 02/26/2014 :  11:06:24  Show Profile  Reply with Quote
the code i'm running is identical to that:

[code]

UPDATE import2262014
SET 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 1
Invalid length parameter passed to the SUBSTRING function.

Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 02/26/2014 :  11:14:55  Show Profile  Reply with Quote
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)_!
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
646 Posts

Posted - 02/26/2014 :  11:15:33  Show Profile  Reply with Quote
that's the whole query
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 02/26/2014 :  11:17:27  Show Profile  Reply with Quote
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)_!
Go to Top of Page

kennejd
Starting Member

USA
11 Posts

Posted - 02/26/2014 :  11:18:16  Show Profile  Reply with Quote
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.
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 02/26/2014 :  11:25:21  Show Profile  Reply with Quote
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)_!
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.08 seconds. Powered By: Snitz Forums 2000