SQL Select part of string!

Question

Post reply

SQL Select part of string!

asterman_3

SSC Eights!

Points: 800
More actions
April 5, 2011 at 9:03 am

#234397

Hi,
I have a string :
"UPDATE Persons
SET Address='xxx', City='yyy'
WHERE xxxID='Test' AND ColumnName2='Test1'".
And I want to extract these words in bold. So, how can I do this in a general way, so it will take everytime these words, in every different query with different parameters? (extract exactly from the "WHERE Statement").
I am more focused on extracting the ID from each UPDATE action that has been taken??
Thank you!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

what you want to do is wrap this up into a stored procedure, and use parameters;

here's an example of the code, and then how to call it: note the procedure has 4 parameters to it.

CREATE PROCEDURE pr_UpdatePersons

(@Address varchar(100),

@City varchar(100),

@ID varchar(30),

@Column2 varchar(30)

)

AS

BEGIN --Begin code

UPDATE Persons

SET [Address] = @Address,

City = @City

WHERE xxxID =@ID

AND ColumnName2 =@Column2

END --PROC

if you were doing this in TSQL, you can use other variables, or hardcode the values:

--assuming all 4 params in the correct order

EXEC pr_UpdatePersons '10501 SW 123 Ave','Miami','42','District 17'

--or

EXEC pr_UpdatePersons @Address='10501 SW 123 Ave',@City='Miami',@ID='42',@Column2='District 17'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

asterman_3 SSC Eights! Points: 800 More actions · Answer 2

I want to select the values mentioned up from the string and here I have an example:

row_id=case when statement like '%UPDATE%'

then Left(SubString(statement, PatIndex('%[0-9]%', statement), 8000), PatIndex('%[^%[^0-9]%', SubString(statement, PatIndex('%[0-9]%', statement), 8000) + 'X')-1)

else QUOTENAME('Unknown')

But this doesn't work since if I have a string for example:

UPDATE Table1

SET xxx = 2021,ModifiedDate = GETDATE()

WHERE xID = 14

It takes number 2021, and how to make it take the NUMBER from the where statement "14", since I want to select the ID number only.