removing partial string from column

how do i update a table which has like two strings in 1 column like

blog, joe ?

i want to strip the joe into a new field and the blog into another field

update Agency
set firstname= substring(firstname,charindex(' ',firstname)+1 ,len(firstname))

i managed to strip the first name which is the string at the back but not the last name which is the string at the frontTry this to get the lastname:
lastname = left(column1, patindex('%,%', column1) - 1)|||The error message was :-

update Agency
set lastname = left(lastname, patindex('%,%', lastname) - 1)

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

Dario D'Alessandro, Dario
Stan Dickson, Stan
Ray John, Ray
Juan Sanchez, Juan

I trying to remove the Dario|||Gotta make sure there is at least one comma, or it errors out. Try this:

set lastname = left(lastname, charindex(',', lastname + ',') - 1)|||Thanks that did actually work.. not sure how that charindex worked. i was using to REPLACE statements to make it work. It was more efficient using your method.|||If you need a method of parsing name strings into component parts, I have a UDF that will do that too.

Removing non-alpha characters & spaces script...

Hi I am trying to strip out any non-alpha characters from a field.

i.e. Field = ABC"_IT8*$ should return: ABCIT8

I am writing a loop to do this for all values of a field. The script runs, but hangs...please could somebody advise on the code below...:

I run the script but it doesn't seem to finish. Can anybody see any issues with the code:

DECLARE @.Index SMALLINT,
@.MATCH_Supplier_name varchar(500),
@.Counter numeric,
@.Max numeric
-- @.sqlstring varchar(500)

SET @.Counter = 1
SET @.Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)

WHILE @.Counter <@.Max
BEGIN
SET @.MATCH_Supplier_name = (SELECT Match_Supplier_Name FROM SUPPLIER_TABLE_TEST WHERE @.Counter = DTect_Supplier_SRN)
SET @.Index = LEN(@.MATCH_Supplier_name)
WHILE @.Index > = 1
SET @.MATCH_Supplier_name = CASE
WHEN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1) LIKE '[a-zA-Z]' TH EN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1)
WHEN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1) LIKE '[0-9]' THEN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1)
ELSE ''
END + @.MATCH_Supplier_name
SET @.Index = @.Index - 1
--PRINT @.MATCH_Supplier_name
SET @.Counter = @.Counter + 1
ENDSET @.Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)

Change it to count ...don't have much time now,check for other errors.|||Cheers, its all sorted now.

Removing Mobile Edition BOL

I'm hoping that someone has any ideas on this. I want to completely strip the entire Books Online module for SQL Server Mobile Edition. Why? It gets really irritating when I try to look up topics and it always yanks up stuff from the Mobile edition and I could care less about those. There are entire sets of topics that BOL will do nothing but pull up the Mobile edition topic which is completely irrelevant for other editions.

Hi Michael,

Yes, you can remove the Mobile edition topics from the help collection. See the Books Online topic "SQL Server 2005 Combined Help Collection Manager " (ms-help://sql90/sqlcc9/html/b06d0f98-ef00-4b03-9f5d-b5c184b8df92.htm).

In that topic under the heading "Available Help Collections", clear the checkbox for SQL Server Mobile and then click Update Combined Help Collection. Close BOL and reopen it and the Mobile topics will be gone from the Index and Search filters.

Regards,

Gail

|||

That would make sense, if it would actually work or point me to anything. Pulling up that help topic throws a debug runtime error message:

n_objPlugInListMSDNArchive.Count is null or not an object

I'm assuming that it is on this page that it actually shows the collections, but since it always throws a runtime error, they obviously don't display. I'm also assuming that it is in this list that will not display that I'll find the check boxes to get rid of the Mobile Edition BOL.

I have now tried this on 11 different machines. 3 of them throw run time debug errors. The other 8 pull up the page which doesn't display any installed collections at all. So, it takes me back to my original question since there doesn't seem to be any working mechanism for getting rid of this Mobile Edition help that continuously garbages up my results.

|||

Hi Michael,

The scripting error you're getting occurs in some scenarios when SQL Server 2005 BOL and the MSDN Library are installed side-by-side. I was told that this error was fixed in the April refresh of SQL Server 2005 Books Online. Updating your version of Books Online to the April or July refresh from here: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx should fix the scripting problem. Afterwards, you should be able to use the page I mentioned earlier correctly.

Let me know if this doesn't solve the problem for you.

Regards,

Gail

Removing Characters in Field grouping

Hello,

I am using the following expression to strip the last 11 characters in a field group and it returns an error saying that Len cannot use a negative number. It must be 0 or greater. Is there a better alternative?

Code Snippet

left(Fields!TestName.Value,len(Fields!TestName.Value)-11))

you can use Rtrim|||

Are you always removing 11 character regardless of the field length or do you simply want to restrict it to a certain size?

For example, the following code restricts the length to 5 characters:

=iif(len(rtrim(Fields!TestDATA.Value)) > 5, mid(rtrim(Fields!TestDATA.Value),1,5), rtrim(Fields!TESTDATA.Value))