?
Solved

Change String

Posted on 2013-05-10
3
Medium Priority
?
432 Views
Last Modified: 2013-05-10
I have a field in a SQL table that I need to update based on ID

The SQL is

Update myTable
set  ExecSQL =  @newSQL
where id = @id

An example of the string I'm passing in from my Web VB code is
usp_EmailsForSpecialtyStates 'MA','ER' True, 1000, 365, False, 1, Special, 1, 0

And all I need to do is change that first TRue to a false so that the new value would be
usp_EmailsForSpecialtyStates 'MA','ER' False, 1000, 365, False, 1, Special, 1, 0

Some caveates are that the first two sections are single quotes so that has to be handled
Also...
The string could come in as
usp_EmailsForSpecialtyStates 'CA,CT,KY,MA','ER' True, 1000, 365, False, 1, Special, 1, 0

It's the first filed after the first two sections (States and Stecialty)

And if that first True is already a false...ignore the update.
0
Comment
Question by:Larry Brister
  • 2
3 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39156900
Hi,

So the fileid in your examples is 1000, no?

Regards
  David

PS Suggest that the multiple states thing is a bit of a pest! It means that the string needs to be properly parsed, and not just count the commas!
0
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 39156973
Hi,

Here is first draft code to parse your string. As you asked in SQL topics, this is in SQL.

use tempdb
go

declare @s varchar( max )
set @s = 'usp_EmailsForSpecialtyStates, ''MA'',''ER'', True, 1000, 365, False, 1, Special, 1, 0'

declare @c int
declare @pc int

-- test parse string into components
while 1 = 1 begin
	set @c = charindex( ',', @s, isnull( @pc, 0 ))

	if @c = 0
		break

	declare @p varchar( max )
	set @p = substring( @s, isnull( @pc, 0 ), @c - isnull( @pc, 0 ))

	if left( @p, 1 ) = ' ' 
		set @p = right( @p, len( @p ) - 1 )

	print @p

	set @pc = @c + 1

end

Open in new window

Note that I've added two commas to your string.

Regards
  David
0
 

Author Closing Comment

by:Larry Brister
ID: 39157031
That's it. Thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question