|
How about the following...
-- 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
Ryan
|
|
|
|
|
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
|
|
|
|
|
Interesting problem. This may be a solution? Say you have a string "abcdef".
<br />
-- place each character in a temp table<br />
declare @temp table (ch char)<br />
insert into @temp values ('a')<br />
insert into @temp values ('b')<br />
insert into @temp values ('c')<br />
insert into @temp values ('d')<br />
insert into @temp values ('e')<br />
insert into @temp values ('f')<br />
<br />
-- some test data<br />
declare @temp2 table (string varchar(10))<br />
insert into @temp2 values ('Sabaschede')<br />
insert into @temp2 values ('aaaaaaaaaa')<br />
insert into @temp2 values ('a1b2c3d4e5')<br />
<br />
-- use a cross join to determine how many times each character appears in the string field<br />
-- you just need to determine that 90% of 'abcdef' is 5 characters<br />
select string<br />
from (<br />
select ch, string, case when charindex(ch,string) > 0 then 1 else 0 end chrcount<br />
from @temp<br />
cross join @temp2<br />
) as t<br />
group by string<br />
having sum(chrcount) >= 5<br />
Ryan
|
|
|
|
|
Hay everyone,
I am using CDOSYS to send emails from my sql server 2000. Now the problem is that when i am sending normal emails it's working fine but when i am sending emails that are larger than 8k it timeouts. By the way the email content is also html.
anyone has any ideas or has delt with this problem before?
Regards,
Christian Pace
|
|
|
|
|
Since CDOSYS is a builtin tool in ASP.NET, you may be more likely to get an answer in the ASP.NET forum instead of the SQL Forum.
|
|
|
|
|
I'm attempting to build a Version calculator that will increment based on a depth field. I need this to execute inside a Stored Proc. My depth is 5.
For example, I have 1.1 so the next time I call the Stored Proc it will be 1.2, 1.3, 1.4, 1.5. After it fills up based on that depth I want it to go down to the next level so the next one would be 1.1.1. The next would be 1.1.2, 1.1.3, etc... Once that bottom version fills out it will only go up one level. ie: 1.2.1, 1.2.2, 1.2.3, ...
|
|
|
|
|
bgates1970 wrote: My depth is 5.
Mine is 1. I'm glad you didn't ask any question, clearly I wouldn't be up to it anyway.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
a 1? You're significant other must be disappointed.
|
|
|
|
|
a big 1, I admit.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
sometimes its best to stay separated from reality
|
|
|
|
|
What's supposed to happen after version 5.5.5.5?
Rename the product?
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
42
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
LOL! This one takes the cake! Do the math! Do you realize the number of versions you would be compiling? By that time I hope the product is obsolete.
|
|
|
|
|
Uhm, .... let's see.
Five times five, that's twenty five. Five times twenty five, well that's just five times five again which is a five and a carry of two. Then you have five times two which is ten, but you have to add the carry of two which is twelve, so that's one hundred and twenty five. Oh man this is starting to get serious. How big is this number going to get. Five times one hundred and twenty five is five times five again that's twenty five which is really just a five with a carry of two. Then there's that five times two again which is ten and the carry of two which is twelve, but that's just a two and a carry of one and finally there's five times one which is five but you have to add that carry of one again to make it a six. There you have it, six hundred and twenty five versions.
You're well on your way.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
I understand Math for some people is very difficult. If you started at 1.1, using a Depth of 3 and started incrementing by 1 it would look like... 1.1, 1.2, 1.3, 1.1.1, 1.1.2, 1.1.3, 1.2.1, 1.2.2, 1.2.3... So 625 versions is right on according to your math. Sorry but using a depth of 5 would be quite a bit higher than 625. You are correct though I have gotten what I expected out of this forum... smart a$$ comments and nothing really substantive.
|
|
|
|
|
bgates1970 wrote: I have gotten what I expected out of this forum
Glad to have helped you out, then. When you have an actual question that you need assistance with, I'll be glad to continue that help.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
bgates1970 wrote: I have gotten what I expected out of this forum... smart a$$ comments and nothing really substantive.
Try rereading your original post. Do you see anything substantive there? Do you see a question?
It's time for a new signature.
|
|
|
|
|
you're making things hard on yourself. Five simply is 10 in base 5. So the fourth power of that is 10000. That's all. No carries involved.
However, it is not the available number of versions, as the early versions would have fewer digits; so the better approximation would be 11110 in base 5. Note that I left out one, as I assume the very first version will have one digit, not zero digits. Unless you forget about version numbers until you actually need them.
It is too bad we never got an actual question about this very interesting subject...
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
Luc Pattyn wrote: Unless you forget about version numbers until you actually need them
You're right. I leave the numbering to my version control provider. Though I understand that someone has to write and maintain the code that implements some numbering scheme.
The OP used the word 'depth' and you are inferring that as 'base 5'. And that may well be the intended assumption, but obviously along with a clear and concise question, there was much more lacking from the post. My use of simple arithmetic was a poor attempt to sarcasticly point that out to him.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
|
1) first of all i want to save a TreeView in database
i designed a table with three fields (ID , NodeName,UpareNodeID)
and create a relation ship with ID and UpareNodeID is it correct?
i want to know if there is a better solution to save and retrieve this kind of collection
2) the second problem : how can i make a relationship from a field to another in the same table in ms access 2007 ?
thanks!
|
|
|
|
|
All the fields in each row in your table, are automatically related to each other, by being in the same row. You can not and do not need to create any relationship.
If you want to relate values from one row to those in another row, you will have to create two tables, and create a relationship between those.
So for example something like:
Table 1: Nodes (ID, NodeName) Where ID is unique
Tabl e2: UpareNodes (ID, NodeName, Parent_ID)
Relation: Nodes.ID = UpareNodes.Parent_ID
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hi,
I'm trying to store RTF data inside of Transact-SQL and have used a data type of TEXT, however when retrieving the data from the database all I am receiving is the actual text, all of the RTF markup is gone and I am just receiving plain text.
My question is what should I be using for a data type, since TEXT is not working the way I thought it would.
Thank you,
Glenn
Glenn
|
|
|
|