Search + Sql server 2005
-
hi, I have to search 90% characters in a string. Suppose the string is "abcdef" and it;s character length is 6 and it's 90 percent character length will be 5 Now i have to search it's any 5 character in the following string string1 stringabcdef astrbincgdef Please suggest something ... Is there any way to do so with loop Thanks
-
hi, I have to search 90% characters in a string. Suppose the string is "abcdef" and it;s character length is 6 and it's 90 percent character length will be 5 Now i have to search it's any 5 character in the following string string1 stringabcdef astrbincgdef Please suggest something ... Is there any way to do so with loop Thanks
For that you need to write either Stored Procedure or Function. Just calculate length of the search string, and also need to calculate how many characters for 90% it. Then you need to search character by character.. Sounds Non-trivial.
Jinal Desai - LIVE Experience is mother of sage....
-
hi, I have to search 90% characters in a string. Suppose the string is "abcdef" and it;s character length is 6 and it's 90 percent character length will be 5 Now i have to search it's any 5 character in the following string string1 stringabcdef astrbincgdef Please suggest something ... Is there any way to do so with loop Thanks
-
hi,
RyanEK wrote:
-- return 90% of a string declare @s varchar(6) set @s = '123456' select substring(@s, 1, cast(floor(0.9 * len(@s)) as int)) -- usage select * from [table] where charindex(substring(@s, 1, cast(floor(0.9 * len(@s)) as int)), [field]) > 0
Thanks for the kind attention. It will work fine if I have to start from position 1 only. But the string to search that I mentioned in the post. was abcdef and it's 90% can be abcde or bcdef similarly i can have larger strings as well and it's propability will be have more string to search. Is it possible to resolve it without any iteration ? Thanks
-
hi,
RyanEK wrote:
-- return 90% of a string declare @s varchar(6) set @s = '123456' select substring(@s, 1, cast(floor(0.9 * len(@s)) as int)) -- usage select * from [table] where charindex(substring(@s, 1, cast(floor(0.9 * len(@s)) as int)), [field]) > 0
Thanks for the kind attention. It will work fine if I have to start from position 1 only. But the string to search that I mentioned in the post. was abcdef and it's 90% can be abcde or bcdef similarly i can have larger strings as well and it's propability will be have more string to search. Is it possible to resolve it without any iteration ? Thanks
-
Ahh I see, you want to match a percentage of a string regardless of its position. I suggest you create a temp table holding all possible values and join to that. eg. 25% of 'ABCDEFGHIJ' would yield a table with the rows: 'AB' 'BC' 'CD' etc... Ryan
hi Ryan, Thanks for the reply. Actually i wanted to search 90% in a non consecutive way. Example the string to search is abcdef it's original length is 6 it's 90% length is 5. and i want to search any five characters from this 90% in a non consecutive way from the following records. "Search Matched" - Non matched because only 4 characters matched "Sabaschede" - matched and found five characters in it. can i do it without using any loop ? And I have millions of records, also have to consider it's performance
-
hi Ryan, Thanks for the reply. Actually i wanted to search 90% in a non consecutive way. Example the string to search is abcdef it's original length is 6 it's 90% length is 5. and i want to search any five characters from this 90% in a non consecutive way from the following records. "Search Matched" - Non matched because only 4 characters matched "Sabaschede" - matched and found five characters in it. can i do it without using any loop ? And I have millions of records, also have to consider it's performance
Interesting problem. This may be a solution? Say you have a string "abcdef".
-- place each character in a temp table declare @temp table (ch char) insert into @temp values ('a') insert into @temp values ('b') insert into @temp values ('c') insert into @temp values ('d') insert into @temp values ('e') insert into @temp values ('f') -- some test data declare @temp2 table (string varchar(10)) insert into @temp2 values ('Sabaschede') insert into @temp2 values ('aaaaaaaaaa') insert into @temp2 values ('a1b2c3d4e5') -- use a cross join to determine how many times each character appears in the string field -- you just need to determine that 90% of 'abcdef' is 5 characters select string from ( select ch, string, case when charindex(ch,string) > 0 then 1 else 0 end chrcount from @temp cross join @temp2 ) as t group by string having sum(chrcount) >= 5Ryan