Search and Replace String Value
-
Hi All, I have a string variable contain text that I would like to search for "Keyword" (AND, OR ) I like to do is search the string varaible strValue for the word "AND" that follow by word "OR" and insert "(" and the last word "OR" and insert ")" .The last "OR" might have "AND" or blank space, The combination could be any order I need to place the "(" and ")" where the "OR" exist. For Example: Orignal strValue = " col1 = "John" and col4 = "smith" and col3 = "John" or col7 <> "closed" or col9 > 1 and col5 = "passed" " After the replace the strValue should be : strValue = " col1 = "John" and col4 = "smith" and (col3 = "John" or col7 <> "closed" or col9 > 1 ) and col5 = "passed" " The strValue then send to SQL store procedure as a where clause in sql statement. Any assistance is greatly apprecaited. please provide codes example. Thanks DocHoliday.
-
Hi All, I have a string variable contain text that I would like to search for "Keyword" (AND, OR ) I like to do is search the string varaible strValue for the word "AND" that follow by word "OR" and insert "(" and the last word "OR" and insert ")" .The last "OR" might have "AND" or blank space, The combination could be any order I need to place the "(" and ")" where the "OR" exist. For Example: Orignal strValue = " col1 = "John" and col4 = "smith" and col3 = "John" or col7 <> "closed" or col9 > 1 and col5 = "passed" " After the replace the strValue should be : strValue = " col1 = "John" and col4 = "smith" and (col3 = "John" or col7 <> "closed" or col9 > 1 ) and col5 = "passed" " The strValue then send to SQL store procedure as a where clause in sql statement. Any assistance is greatly apprecaited. please provide codes example. Thanks DocHoliday.
It's probably not ideal to be dynamically generating WHERE clauses and passing the to your procedures. Could you post the Stored Proc code as there might be an easier way round this?
-
It's probably not ideal to be dynamically generating WHERE clauses and passing the to your procedures. Could you post the Stored Proc code as there might be an easier way round this?
Hi, I started writing a function to do the replace/insert but ran into issue when there are more then one "AND" then follow by "OR", the "(" get insert after the first "AND". Also when there blank space after the "OR" at the end of the string the function fail. Thanks, DocHoilday Function codes: Private Function ReplaceString(ByVal strValue As String) As String Dim andIndex As Integer = strValue.IndexOf("AND") Dim blankIndex As Integer = strValue.IndexOf("Blank") Dim orIndex As Integer = strValue.IndexOf("OR") Dim nextAndIndex As Integer = strValue.IndexOf("AND", andIndex + 3) Dim secondBlankIndex As Integer = strValue.IndexOf("Blank", blankIndex + 5) Dim temp As String = String.Empty if orIndex > andIndex AndAlso orIndex < secondAndIndex Then temp = strValue.Substring(0, andIndex + 3) + " (" temp += strValue.Substring(andIndex + 3, secondAndIndex - andIndex - 3).Trim() + ") " temp += strValue.Substring(secondAndIndex) ElseIf orIndex > andIndex AndAlso orIndex < secondBlankIndex Then temp = strValue.Substring(0, andIndex + 3) + " (" temp += strValue.Substring(blankIndex + 5, secondBlankIndex - blankIndex - 5).Trim() + ") " temp += strValue.Substring(secondBlankIndex) End If Return temp End Function Here is the SP code: CREATE PROCEDURE Get_Build_My_Report ( @Application_id CHAR(5), @DisplayColumn VARCHAR(2000), @Filters VARCHAR(1000) = NULL ) AS DECLARE @Select VARCHAR(60) DECLARE @FROM VARCHAR(60) DECLARE @JOIN VARCHAR (2000) DECLARE @WHERE VARCHAR(60) DECLARE @TempColumnName VARCHAR(1000) DECLARE @Var VARCHAR(1000) SET @Select = 'SELECT' SET @FROM = 'FROM Issues_Tracking it' SET @JOIN = 'LEFT JOIN User_mstr um ON it.Assigned_To_id = um.userid AND it.application_id = um.application_id LEFT JOIN Status s ON it.status = s.status_id LEFT JOIN NGModules m ON it.module = m.mod_id AND it.application_id = m.application_id LEFT JOIN Rank_level rl ON it.rank = rl.rank_id LEFT JOIN NGProducts p ON it.product = p.product_id LEFT JOIN Request_Type rt ON it.Request_Type = rt.Request_Type_id LEFT JOIN practice pr ON it.Practice_id = pr.practice_id LEFT JOIN Response r ON it.Submitted_to_Vendor = r.Answer_id LEFT JOIN Response r2 ON it.Issues_Known_To_Vendor = r2.Answer_id LEFT JOIN Environment e ON it.Environment = e.Environment_id
-
Hi, I started writing a function to do the replace/insert but ran into issue when there are more then one "AND" then follow by "OR", the "(" get insert after the first "AND". Also when there blank space after the "OR" at the end of the string the function fail. Thanks, DocHoilday Function codes: Private Function ReplaceString(ByVal strValue As String) As String Dim andIndex As Integer = strValue.IndexOf("AND") Dim blankIndex As Integer = strValue.IndexOf("Blank") Dim orIndex As Integer = strValue.IndexOf("OR") Dim nextAndIndex As Integer = strValue.IndexOf("AND", andIndex + 3) Dim secondBlankIndex As Integer = strValue.IndexOf("Blank", blankIndex + 5) Dim temp As String = String.Empty if orIndex > andIndex AndAlso orIndex < secondAndIndex Then temp = strValue.Substring(0, andIndex + 3) + " (" temp += strValue.Substring(andIndex + 3, secondAndIndex - andIndex - 3).Trim() + ") " temp += strValue.Substring(secondAndIndex) ElseIf orIndex > andIndex AndAlso orIndex < secondBlankIndex Then temp = strValue.Substring(0, andIndex + 3) + " (" temp += strValue.Substring(blankIndex + 5, secondBlankIndex - blankIndex - 5).Trim() + ") " temp += strValue.Substring(secondBlankIndex) End If Return temp End Function Here is the SP code: CREATE PROCEDURE Get_Build_My_Report ( @Application_id CHAR(5), @DisplayColumn VARCHAR(2000), @Filters VARCHAR(1000) = NULL ) AS DECLARE @Select VARCHAR(60) DECLARE @FROM VARCHAR(60) DECLARE @JOIN VARCHAR (2000) DECLARE @WHERE VARCHAR(60) DECLARE @TempColumnName VARCHAR(1000) DECLARE @Var VARCHAR(1000) SET @Select = 'SELECT' SET @FROM = 'FROM Issues_Tracking it' SET @JOIN = 'LEFT JOIN User_mstr um ON it.Assigned_To_id = um.userid AND it.application_id = um.application_id LEFT JOIN Status s ON it.status = s.status_id LEFT JOIN NGModules m ON it.module = m.mod_id AND it.application_id = m.application_id LEFT JOIN Rank_level rl ON it.rank = rl.rank_id LEFT JOIN NGProducts p ON it.product = p.product_id LEFT JOIN Request_Type rt ON it.Request_Type = rt.Request_Type_id LEFT JOIN practice pr ON it.Practice_id = pr.practice_id LEFT JOIN Response r ON it.Submitted_to_Vendor = r.Answer_id LEFT JOIN Response r2 ON it.Issues_Known_To_Vendor = r2.Answer_id LEFT JOIN Environment e ON it.Environment = e.Environment_id
I forgot to mentioned that the @Filters varaible is the where clause.
-
Hi, I started writing a function to do the replace/insert but ran into issue when there are more then one "AND" then follow by "OR", the "(" get insert after the first "AND". Also when there blank space after the "OR" at the end of the string the function fail. Thanks, DocHoilday Function codes: Private Function ReplaceString(ByVal strValue As String) As String Dim andIndex As Integer = strValue.IndexOf("AND") Dim blankIndex As Integer = strValue.IndexOf("Blank") Dim orIndex As Integer = strValue.IndexOf("OR") Dim nextAndIndex As Integer = strValue.IndexOf("AND", andIndex + 3) Dim secondBlankIndex As Integer = strValue.IndexOf("Blank", blankIndex + 5) Dim temp As String = String.Empty if orIndex > andIndex AndAlso orIndex < secondAndIndex Then temp = strValue.Substring(0, andIndex + 3) + " (" temp += strValue.Substring(andIndex + 3, secondAndIndex - andIndex - 3).Trim() + ") " temp += strValue.Substring(secondAndIndex) ElseIf orIndex > andIndex AndAlso orIndex < secondBlankIndex Then temp = strValue.Substring(0, andIndex + 3) + " (" temp += strValue.Substring(blankIndex + 5, secondBlankIndex - blankIndex - 5).Trim() + ") " temp += strValue.Substring(secondBlankIndex) End If Return temp End Function Here is the SP code: CREATE PROCEDURE Get_Build_My_Report ( @Application_id CHAR(5), @DisplayColumn VARCHAR(2000), @Filters VARCHAR(1000) = NULL ) AS DECLARE @Select VARCHAR(60) DECLARE @FROM VARCHAR(60) DECLARE @JOIN VARCHAR (2000) DECLARE @WHERE VARCHAR(60) DECLARE @TempColumnName VARCHAR(1000) DECLARE @Var VARCHAR(1000) SET @Select = 'SELECT' SET @FROM = 'FROM Issues_Tracking it' SET @JOIN = 'LEFT JOIN User_mstr um ON it.Assigned_To_id = um.userid AND it.application_id = um.application_id LEFT JOIN Status s ON it.status = s.status_id LEFT JOIN NGModules m ON it.module = m.mod_id AND it.application_id = m.application_id LEFT JOIN Rank_level rl ON it.rank = rl.rank_id LEFT JOIN NGProducts p ON it.product = p.product_id LEFT JOIN Request_Type rt ON it.Request_Type = rt.Request_Type_id LEFT JOIN practice pr ON it.Practice_id = pr.practice_id LEFT JOIN Response r ON it.Submitted_to_Vendor = r.Answer_id LEFT JOIN Response r2 ON it.Issues_Known_To_Vendor = r2.Answer_id LEFT JOIN Environment e ON it.Environment = e.Environment_id
Hi, I sort of see what you're trying to do here but I think there are neater ways. You're not really going to get the benefits of stored procedures but building up your sql like this and it's going to be very difficult to maintain. I think you're trying to get one procedure to cater for all possible data access that you might want at any point in your application. How many different combinations of data do you need from exactly these tables? I'd recommend splitting this procedure into several more specific procedures and then calling the procedures with strongly typed parameters. Or if you're building a much bigger system try reading up on ORM. If you want to post a typical query that you might need from this database I can have a look at reformatting it for you.
-
Hi, I sort of see what you're trying to do here but I think there are neater ways. You're not really going to get the benefits of stored procedures but building up your sql like this and it's going to be very difficult to maintain. I think you're trying to get one procedure to cater for all possible data access that you might want at any point in your application. How many different combinations of data do you need from exactly these tables? I'd recommend splitting this procedure into several more specific procedures and then calling the procedures with strongly typed parameters. Or if you're building a much bigger system try reading up on ORM. If you want to post a typical query that you might need from this database I can have a look at reformatting it for you.
Hi, I am basically building a page that allow user to create their own report. On the page I got all the columns of the table displayed for user to pick to show on the report. Next if they wish do a drill down then they would select criteria as they select the criteria(s) I need to group the "AND" and "OR" to send to the store procedure as one query. Thanks, DocHoliday
-
Hi, I am basically building a page that allow user to create their own report. On the page I got all the columns of the table displayed for user to pick to show on the report. Next if they wish do a drill down then they would select criteria as they select the criteria(s) I need to group the "AND" and "OR" to send to the store procedure as one query. Thanks, DocHoliday
OK, so is the report a datagrid bound to the dataset created by this query? Why don't you try something like this: Create a view of your main query which returns all possible columns you might want to display on the report. Then in your stored procedure have a parameter for each column you might want to filter on.
@col1 varchar(50) = null,
@col2 int = null,
@col3 bit = nullSELECT col1, col2, coln
FROM view1
WHERE 1 = 1
AND isNull(@col1, col1) = col1
AND isNull(@col2, col2) = col2
AND isNull(@col3, col3) = col3Then depending on what the user has requested by drilling down, show/hide columns in the datagrid and pass in only the parameters that you want in the where clause. The isNull() will ignore any parameters you pass in as null and so won't take effect. If you want OR's you could run the procedure multiple times and join the datasets that are returned.
-
OK, so is the report a datagrid bound to the dataset created by this query? Why don't you try something like this: Create a view of your main query which returns all possible columns you might want to display on the report. Then in your stored procedure have a parameter for each column you might want to filter on.
@col1 varchar(50) = null,
@col2 int = null,
@col3 bit = nullSELECT col1, col2, coln
FROM view1
WHERE 1 = 1
AND isNull(@col1, col1) = col1
AND isNull(@col2, col2) = col2
AND isNull(@col3, col3) = col3Then depending on what the user has requested by drilling down, show/hide columns in the datagrid and pass in only the parameters that you want in the where clause. The isNull() will ignore any parameters you pass in as null and so won't take effect. If you want OR's you could run the procedure multiple times and join the datasets that are returned.
Can you show example regarding the OR's having run the procedure multiple times and join the datasets that are returned ?
-
Can you show example regarding the OR's having run the procedure multiple times and join the datasets that are returned ?
You could try this: http://msdn.microsoft.com/en-us/library/system.data.dataset.merge(v=VS.71).aspx[^]
-
You could try this: http://msdn.microsoft.com/en-us/library/system.data.dataset.merge(v=VS.71).aspx[^]
Thanks