Reading through a string parameter
-
Right now I am reading through a string and using it for querying data.
I need to change the string layout and not sure how to do different functions based on different characters. Can I get some ideas and examples on how to accomplish this task? Below is sample string I need to be able to read.
@string = 'datetime~ColumnSearchCriteria:search1,search2,ect~ColumnSearchCriteria:search1,search2,ect.....#'
This is an example of what I was doing just to walk through the string, but will not work if I have to check for different characters to see what I need to populate and use
set @startPosition = 1
set @wavePosition = 0
set @totalLength = LEN(rtrim(ltrim(@string)))
set @wavePosition = CHARINDEX('~', @string, @startPosition)
set @v_srcdt = rtrim(ltrim(SUBSTRING(@string, @startPosition, @wavePosition - @startPosition)))
while (@startPosition < @totalLength)
BEGIN
set @startPosition = @wavePosition + 1
set @wavePosition = CHARINDEX('~', @string, @startPosition)
set @v_updcol = rtrim(ltrim(SUBSTRING(@string, @startPosition, @wavePosition - @startPosition)))
set @startPosition = @wavePosition + 1
set @wavePosition = CHARINDEX('~', @string, @startPosition)
set @v_srchtyp = rtrim(ltrim(SUBSTRING(@string, @startPosition, @wavePosition - @startPosition)))
set @startPosition = @wavePosition + 1
......search condition.....
update table set @v_updcol = @v_srchtyp.....
END
-
Hi jthill,
That's as clear as mud to me.
If you post an example of what you're trying to do (i.e. table structure, sample data, and expected results), you'll get an answer in no time...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
It seems like you are searching for a parser/tokenizer, not a RDBMS solution.
Build/download/buy a parser that puts your data into a DB then do a SELECT with a nice WHERE that can use indexes...
//Hans
-
Here is a sample of what I currently do with the string. It also shows what the string will look like. I just need some ideas to adjust the code to handle it. Thanks for your help.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
DECLARE @FormData varchar(1680)
--Parsing controls
declare @startPosition int
declare @wavePosition int
declare @totalLength int
declare @subStringLen int
declare @StartPt int
DECLARE @v_srcdt DATETIME
DECLARE @v_srchtyp VARCHAR(50)
DECLARE @v_updcol VARCHAR(50)
DECLARE @v_searchtype VARCHAR(150)
DECLARE @v_query NVARCHAR(500)
DECLARE @v_dupquery NVARCHAR(500)
DECLARE @v_dupcnt INT
DECLARE @v_dupcol VARCHAR(50)
-- current string looks like
set
@formdata = '01/01/2000~col1~data1~col2~data2~col3~data3~'
-- I need to be able to handle this string
set @formdata = '01/01/2000~col1:data1,data2,data3~col2:data1,data2,data3~col3:data1,data2,data3#'
-- I need to take use the same column name for each data. When I come upon the next column start process over again.
-- It should stop reading the string when it reaches '#'.
BEGIN
SET NOCOUNT ON
CREATE TABLE #TEMP_TBL1 (ROW_NUM INT IDENTITY, UPDCOL VARCHAR(25), GUID VARCHAR(50), OTHER VARCHAR(150), TEXT_COMMENTS VARCHAR(1500))
--Set default parsing values
set @startPosition = 1
set @wavePosition = 0
set @totalLength = LEN(rtrim(ltrim(@FormData)))
set @wavePosition = CHARINDEX('~', @FormData, @startPosition)
set @v_srcdt = rtrim(ltrim(SUBSTRING(@FormData, @startPosition, @wavePosition - @startPosition)))
while (@startPosition < @totalLength)
BEGIN
set @startPosition = @wavePosition + 1
set @wavePosition = CHARINDEX('~', @FormData, @startPosition)
set @v_updcol = rtrim(ltrim(SUBSTRING(@FormData, @startPosition, @wavePosition - @startPosition)))
set @startPosition = @wavePosition + 1
set @wavePosition = CHARINDEX('~', @FormData, @startPosition)
set @v_srchtyp = rtrim(ltrim(SUBSTRING(@FormData, @startPosition, @wavePosition - @startPosition)))
set @startPosition = @wavePosition + 1
SELECT @v_searchtype = '"' + @v_srchtyp + '"'
INSERT #TEMP_TBL1
(UPDCOL, GUID, OTHER, Text_comments )
SELECT DISTINCT @v_updcol, CONVERT(VARCHAR(50),s.guid), @v_srchtyp, CONVERT(VARCHAR(1860),s.description)
FROM dbo.FullTextSearchByText(@v_searchtype, 0) t
INNER JOIN [dbo].[ViewTable] s ON s.GUID = t.GUID
WHERE s.When >= @v_srcdt
-- updates column with column name in the string --
SET @v_query = 'UPDATE #TEMP_TBL1 SET '+
@v_updcol +' = '''+@v_srchtyp +
''' WHERE updcol = '''+@v_updcol+''''
EXEC sp_executeSQL @v_query
END
select *
from #temp_tbl1
DROP TABLE #temp_tbl1
end
go
-
Hmm. Okay. But why are you doing this with T-SQL? Is this something you need to regularly do? Where does the string come from? Where do you want to put it?
Why not use some scripting language, SPLIT your string on ~, SPLIT all substrings on the COMMA and do whatever you want with your data?
//Hans
-
Hans is right, jthill. This is not something you really should be doing with T-SQL.
Why don't you take a step back, describe what it is you want to achieve (i.e. not how you want to achieve it), and the people here will be able to recommend the best way to go about doing it...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
This is what I need to do.
The customer wants an EXCEL spreadsheet with predetermine columns and the selected columns by the user populated.
The front end they want is the ability to specify which key words and columns (to populate) to do a full text search on XML, DOC, and summary columns. When rows do come back I need to populate the column identified by the user with the key words. If the same row returns multiple times for different key word(s) and/or column(s) I need the existing row updated.
This will be run daily. Does this help?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply