Rule of thumb about number of attributes and data points?

This is a general question about data modeling. I'm more curious than anything else.

There is much talk about over-training data model, and I'm sure there are under training as well. As a rule of thumb, depending on the algorithm, what is a good ratio of attributes vs data points?

-Young K

Overtraining basically means that you have a model that fit your training data very well. But the model performs very badly on unseen data. For example, if you are training a decision tree, the alogrithm you use may generate a decision tree to fit your training data perfectly. This can be easily achived by keep creating split until each node only contains data points from one sepcific class. In general, this kind of decision does not generalize well on new data. Let's use an extreme case, say, you wish to predict the weather. You have collected the weather data of the past 10 years. And you accidently choose date as input, and the Decision Tree algorithm generate a tree like this:

date

|

-

| |

Nov 15, 2006 ... Nov 15, 2005 ...

| |

Rainny Sunny

This tree is 100% correct on historical data, but totally useless on predict weather data.

Basically, Over training is a common issue for all algorithms, and a lot of techniques have been applied to reduce to risk of overtraining. The most common traditional one is Occam's razor, which prefer to use simpler models. More recently, Bagging and Boosting are also commonly used to address overtraining problem.

In pratical application, you can validate whether or not your model is overfitting by reserving part of your data as indepent testing, and only use the remaining data to train your model, while validate the accuracy of your model with the testing data.

As to the ratio of attribute number vs data points, there is no absolute requirement, although most algorithms perform better with larger training data.

Good luck,

|||Does SQLServer 2005 have any support for boosting or bagging?
|||SQL Server doesn't have built in support for Bagging and Boosting. One way you can determine "how much" data you need for your particular problem is to reserve a test set and then create models with different amounts on training data. For example 1000, 2000, 4000, 8000, 16000, etc. cases. Graph the accuracy results of the models and see where the accuracy "flattens out". The point where accuracy stops increasing with additional data is the "ideal" amount of data for that particular problem.

Rule disappeared

I seem to have a rule on one of my columns which prevents me from entering a
value above 25000 in an integer column.
I am not able to find the rule so that I can delete it.
Is there a system table that I should look for?
This table was upsize from Access to SQL 7.0
Are you sure it's a rule you are looking for? If you used
the upsizing wizard, it should have implemented this as a
constraint.
What are you using to manage SQL Server and where are you
looking? You really don't want to address this by modifying
system tables.
To view information as well as constraints, rules on a
table, you could execute:
EXEC sp_help YourTable
To check for constraints, you can execute:
EXEC sp_helpconstraint YourTable
-Sue
On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:

>I seem to have a rule on one of my columns which prevents me from entering a
>value above 25000 in an integer column.
>I am not able to find the rule so that I can delete it.
>Is there a system table that I should look for?
>This table was upsize from Access to SQL 7.0
|||Sue,
I executed the two command that you suggested. There seem to be another
hidden constraint that I can't find.
"Sue Hoegemeier" wrote:

> Are you sure it's a rule you are looking for? If you used
> the upsizing wizard, it should have implemented this as a
> constraint.
> What are you using to manage SQL Server and where are you
> looking? You really don't want to address this by modifying
> system tables.
> To view information as well as constraints, rules on a
> table, you could execute:
> EXEC sp_help YourTable
> To check for constraints, you can execute:
> EXEC sp_helpconstraint YourTable
> -Sue
> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
>
>
|||Constraints don't typically hide so...let's start over.
Could you please post what you are doing and what tool or
application you are using? And post the exact error message
you are receiving?
-Sue
On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>I executed the two command that you suggested. There seem to be another
>hidden constraint that I can't find.
>"Sue Hoegemeier" wrote:
|||Sue,
I am inside enterprise manager. I try to change the quantity from 6000 to
26000 in an integer column. I get a message that says the current Maximum is
25,000.
We create a new column called quantityNew and with the same datatype and
content. QuantityNew can be changed with no problems.
Arne
"Sue Hoegemeier" wrote:

> Constraints don't typically hide so...let's start over.
> Could you please post what you are doing and what tool or
> application you are using? And post the exact error message
> you are receiving?
> -Sue
> On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
>
>
|||The only way I can think of that you can get a message like
that is through a trigger. If it was a rule or a constraint,
you'd get a much more verbose message indicating that it
violated a check constraint or a rule, the column it was on,
the name of the constraint if it violated a check
constraint, etc.
Check for triggers on the table. In Enterprise Manager,
right click on the table, select All Tasks and then select
manage triggers.
-Sue
On Thu, 20 Jan 2005 10:45:03 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>I am inside enterprise manager. I try to change the quantity from 6000 to
>26000 in an integer column. I get a message that says the current Maximum is
>25,000.
>We create a new column called quantityNew and with the same datatype and
>content. QuantityNew can be changed with no problems.
>Arne
>"Sue Hoegemeier" wrote:
|||Sue,
I finally found the offending trigger. Thanks.
Arne.
"Sue Hoegemeier" wrote:

> The only way I can think of that you can get a message like
> that is through a trigger. If it was a rule or a constraint,
> you'd get a much more verbose message indicating that it
> violated a check constraint or a rule, the column it was on,
> the name of the constraint if it violated a check
> constraint, etc.
> Check for triggers on the table. In Enterprise Manager,
> right click on the table, select All Tasks and then select
> manage triggers.
> -Sue
> On Thu, 20 Jan 2005 10:45:03 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
>
>
|||Glad it's fixed! Thanks for posting back the results.
-Sue
On Thu, 20 Jan 2005 13:43:09 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>I finally found the offending trigger. Thanks.
>Arne.
>"Sue Hoegemeier" wrote:

Rule disappeared

I seem to have a rule on one of my columns which prevents me from entering a
value above 25000 in an integer column.
I am not able to find the rule so that I can delete it.
Is there a system table that I should look for?
This table was upsize from Access to SQL 7.0Are you sure it's a rule you are looking for? If you used
the upsizing wizard, it should have implemented this as a
constraint.
What are you using to manage SQL Server and where are you
looking? You really don't want to address this by modifying
system tables.
To view information as well as constraints, rules on a
table, you could execute:
EXEC sp_help YourTable
To check for constraints, you can execute:
EXEC sp_helpconstraint YourTable
-Sue
On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
>I seem to have a rule on one of my columns which prevents me from entering a
>value above 25000 in an integer column.
>I am not able to find the rule so that I can delete it.
>Is there a system table that I should look for?
>This table was upsize from Access to SQL 7.0|||Sue,
I executed the two command that you suggested. There seem to be another
hidden constraint that I can't find.
"Sue Hoegemeier" wrote:
> Are you sure it's a rule you are looking for? If you used
> the upsizing wizard, it should have implemented this as a
> constraint.
> What are you using to manage SQL Server and where are you
> looking? You really don't want to address this by modifying
> system tables.
> To view information as well as constraints, rules on a
> table, you could execute:
> EXEC sp_help YourTable
> To check for constraints, you can execute:
> EXEC sp_helpconstraint YourTable
> -Sue
> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
> >I seem to have a rule on one of my columns which prevents me from entering a
> >value above 25000 in an integer column.
> >I am not able to find the rule so that I can delete it.
> >Is there a system table that I should look for?
> >This table was upsize from Access to SQL 7.0
>|||Constraints don't typically hide so...let's start over.
Could you please post what you are doing and what tool or
application you are using? And post the exact error message
you are receiving?
-Sue
On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
>Sue,
>I executed the two command that you suggested. There seem to be another
>hidden constraint that I can't find.
>"Sue Hoegemeier" wrote:
>> Are you sure it's a rule you are looking for? If you used
>> the upsizing wizard, it should have implemented this as a
>> constraint.
>> What are you using to manage SQL Server and where are you
>> looking? You really don't want to address this by modifying
>> system tables.
>> To view information as well as constraints, rules on a
>> table, you could execute:
>> EXEC sp_help YourTable
>> To check for constraints, you can execute:
>> EXEC sp_helpconstraint YourTable
>> -Sue
>> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
>> <Arne@.discussions.microsoft.com> wrote:
>> >I seem to have a rule on one of my columns which prevents me from entering a
>> >value above 25000 in an integer column.
>> >I am not able to find the rule so that I can delete it.
>> >Is there a system table that I should look for?
>> >This table was upsize from Access to SQL 7.0
>>|||Sue,
I am inside enterprise manager. I try to change the quantity from 6000 to
26000 in an integer column. I get a message that says the current Maximum is
25,000.
We create a new column called quantityNew and with the same datatype and
content. QuantityNew can be changed with no problems.
Arne
"Sue Hoegemeier" wrote:
> Constraints don't typically hide so...let's start over.
> Could you please post what you are doing and what tool or
> application you are using? And post the exact error message
> you are receiving?
> -Sue
> On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
> >Sue,
> >I executed the two command that you suggested. There seem to be another
> >hidden constraint that I can't find.
> >
> >"Sue Hoegemeier" wrote:
> >
> >> Are you sure it's a rule you are looking for? If you used
> >> the upsizing wizard, it should have implemented this as a
> >> constraint.
> >> What are you using to manage SQL Server and where are you
> >> looking? You really don't want to address this by modifying
> >> system tables.
> >> To view information as well as constraints, rules on a
> >> table, you could execute:
> >> EXEC sp_help YourTable
> >> To check for constraints, you can execute:
> >> EXEC sp_helpconstraint YourTable
> >>
> >> -Sue
> >>
> >> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
> >> <Arne@.discussions.microsoft.com> wrote:
> >>
> >> >I seem to have a rule on one of my columns which prevents me from entering a
> >> >value above 25000 in an integer column.
> >> >I am not able to find the rule so that I can delete it.
> >> >Is there a system table that I should look for?
> >> >This table was upsize from Access to SQL 7.0
> >>
> >>
>|||The only way I can think of that you can get a message like
that is through a trigger. If it was a rule or a constraint,
you'd get a much more verbose message indicating that it
violated a check constraint or a rule, the column it was on,
the name of the constraint if it violated a check
constraint, etc.
Check for triggers on the table. In Enterprise Manager,
right click on the table, select All Tasks and then select
manage triggers.
-Sue
On Thu, 20 Jan 2005 10:45:03 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
>Sue,
>I am inside enterprise manager. I try to change the quantity from 6000 to
>26000 in an integer column. I get a message that says the current Maximum is
>25,000.
>We create a new column called quantityNew and with the same datatype and
>content. QuantityNew can be changed with no problems.
>Arne
>"Sue Hoegemeier" wrote:
>> Constraints don't typically hide so...let's start over.
>> Could you please post what you are doing and what tool or
>> application you are using? And post the exact error message
>> you are receiving?
>> -Sue
>> On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
>> <Arne@.discussions.microsoft.com> wrote:
>> >Sue,
>> >I executed the two command that you suggested. There seem to be another
>> >hidden constraint that I can't find.
>> >
>> >"Sue Hoegemeier" wrote:
>> >
>> >> Are you sure it's a rule you are looking for? If you used
>> >> the upsizing wizard, it should have implemented this as a
>> >> constraint.
>> >> What are you using to manage SQL Server and where are you
>> >> looking? You really don't want to address this by modifying
>> >> system tables.
>> >> To view information as well as constraints, rules on a
>> >> table, you could execute:
>> >> EXEC sp_help YourTable
>> >> To check for constraints, you can execute:
>> >> EXEC sp_helpconstraint YourTable
>> >>
>> >> -Sue
>> >>
>> >> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
>> >> <Arne@.discussions.microsoft.com> wrote:
>> >>
>> >> >I seem to have a rule on one of my columns which prevents me from entering a
>> >> >value above 25000 in an integer column.
>> >> >I am not able to find the rule so that I can delete it.
>> >> >Is there a system table that I should look for?
>> >> >This table was upsize from Access to SQL 7.0
>> >>
>> >>
>>|||Sue,
I finally found the offending trigger. Thanks.
Arne.
"Sue Hoegemeier" wrote:
> The only way I can think of that you can get a message like
> that is through a trigger. If it was a rule or a constraint,
> you'd get a much more verbose message indicating that it
> violated a check constraint or a rule, the column it was on,
> the name of the constraint if it violated a check
> constraint, etc.
> Check for triggers on the table. In Enterprise Manager,
> right click on the table, select All Tasks and then select
> manage triggers.
> -Sue
> On Thu, 20 Jan 2005 10:45:03 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
> >Sue,
> >I am inside enterprise manager. I try to change the quantity from 6000 to
> >26000 in an integer column. I get a message that says the current Maximum is
> >25,000.
> >We create a new column called quantityNew and with the same datatype and
> >content. QuantityNew can be changed with no problems.
> >
> >Arne
> >
> >"Sue Hoegemeier" wrote:
> >
> >> Constraints don't typically hide so...let's start over.
> >> Could you please post what you are doing and what tool or
> >> application you are using? And post the exact error message
> >> you are receiving?
> >>
> >> -Sue
> >>
> >> On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
> >> <Arne@.discussions.microsoft.com> wrote:
> >>
> >> >Sue,
> >> >I executed the two command that you suggested. There seem to be another
> >> >hidden constraint that I can't find.
> >> >
> >> >"Sue Hoegemeier" wrote:
> >> >
> >> >> Are you sure it's a rule you are looking for? If you used
> >> >> the upsizing wizard, it should have implemented this as a
> >> >> constraint.
> >> >> What are you using to manage SQL Server and where are you
> >> >> looking? You really don't want to address this by modifying
> >> >> system tables.
> >> >> To view information as well as constraints, rules on a
> >> >> table, you could execute:
> >> >> EXEC sp_help YourTable
> >> >> To check for constraints, you can execute:
> >> >> EXEC sp_helpconstraint YourTable
> >> >>
> >> >> -Sue
> >> >>
> >> >> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
> >> >> <Arne@.discussions.microsoft.com> wrote:
> >> >>
> >> >> >I seem to have a rule on one of my columns which prevents me from entering a
> >> >> >value above 25000 in an integer column.
> >> >> >I am not able to find the rule so that I can delete it.
> >> >> >Is there a system table that I should look for?
> >> >> >This table was upsize from Access to SQL 7.0
> >> >>
> >> >>
> >>
> >>
>|||Glad it's fixed! Thanks for posting back the results.
-Sue
On Thu, 20 Jan 2005 13:43:09 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
>Sue,
>I finally found the offending trigger. Thanks.
>Arne.
>"Sue Hoegemeier" wrote:
>> The only way I can think of that you can get a message like
>> that is through a trigger. If it was a rule or a constraint,
>> you'd get a much more verbose message indicating that it
>> violated a check constraint or a rule, the column it was on,
>> the name of the constraint if it violated a check
>> constraint, etc.
>> Check for triggers on the table. In Enterprise Manager,
>> right click on the table, select All Tasks and then select
>> manage triggers.
>> -Sue
>> On Thu, 20 Jan 2005 10:45:03 -0800, "Arne"
>> <Arne@.discussions.microsoft.com> wrote:
>> >Sue,
>> >I am inside enterprise manager. I try to change the quantity from 6000 to
>> >26000 in an integer column. I get a message that says the current Maximum is
>> >25,000.
>> >We create a new column called quantityNew and with the same datatype and
>> >content. QuantityNew can be changed with no problems.
>> >
>> >Arne
>> >
>> >"Sue Hoegemeier" wrote:
>> >
>> >> Constraints don't typically hide so...let's start over.
>> >> Could you please post what you are doing and what tool or
>> >> application you are using? And post the exact error message
>> >> you are receiving?
>> >>
>> >> -Sue
>> >>
>> >> On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
>> >> <Arne@.discussions.microsoft.com> wrote:
>> >>
>> >> >Sue,
>> >> >I executed the two command that you suggested. There seem to be another
>> >> >hidden constraint that I can't find.
>> >> >
>> >> >"Sue Hoegemeier" wrote:
>> >> >
>> >> >> Are you sure it's a rule you are looking for? If you used
>> >> >> the upsizing wizard, it should have implemented this as a
>> >> >> constraint.
>> >> >> What are you using to manage SQL Server and where are you
>> >> >> looking? You really don't want to address this by modifying
>> >> >> system tables.
>> >> >> To view information as well as constraints, rules on a
>> >> >> table, you could execute:
>> >> >> EXEC sp_help YourTable
>> >> >> To check for constraints, you can execute:
>> >> >> EXEC sp_helpconstraint YourTable
>> >> >>
>> >> >> -Sue
>> >> >>
>> >> >> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
>> >> >> <Arne@.discussions.microsoft.com> wrote:
>> >> >>
>> >> >> >I seem to have a rule on one of my columns which prevents me from entering a
>> >> >> >value above 25000 in an integer column.
>> >> >> >I am not able to find the rule so that I can delete it.
>> >> >> >Is there a system table that I should look for?
>> >> >> >This table was upsize from Access to SQL 7.0
>> >> >>
>> >> >>
>> >>
>> >>
>>

Rule disappeared

I seem to have a rule on one of my columns which prevents me from entering a
value above 25000 in an integer column.
I am not able to find the rule so that I can delete it.
Is there a system table that I should look for?
This table was upsize from Access to SQL 7.0Are you sure it's a rule you are looking for? If you used
the upsizing wizard, it should have implemented this as a
constraint.
What are you using to manage SQL Server and where are you
looking? You really don't want to address this by modifying
system tables.
To view information as well as constraints, rules on a
table, you could execute:
EXEC sp_help YourTable
To check for constraints, you can execute:
EXEC sp_helpconstraint YourTable
-Sue
On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:

>I seem to have a rule on one of my columns which prevents me from entering
a
>value above 25000 in an integer column.
>I am not able to find the rule so that I can delete it.
>Is there a system table that I should look for?
>This table was upsize from Access to SQL 7.0|||Sue,
I executed the two command that you suggested. There seem to be another
hidden constraint that I can't find.
"Sue Hoegemeier" wrote:

> Are you sure it's a rule you are looking for? If you used
> the upsizing wizard, it should have implemented this as a
> constraint.
> What are you using to manage SQL Server and where are you
> looking? You really don't want to address this by modifying
> system tables.
> To view information as well as constraints, rules on a
> table, you could execute:
> EXEC sp_help YourTable
> To check for constraints, you can execute:
> EXEC sp_helpconstraint YourTable
> -Sue
> On Wed, 19 Jan 2005 08:51:06 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
>
>|||Constraints don't typically hide so...let's start over.
Could you please post what you are doing and what tool or
application you are using? And post the exact error message
you are receiving?
-Sue
On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>I executed the two command that you suggested. There seem to be another
>hidden constraint that I can't find.
>"Sue Hoegemeier" wrote:
>|||Sue,
I am inside enterprise manager. I try to change the quantity from 6000 to
26000 in an integer column. I get a message that says the current Maximum is
25,000.
We create a new column called quantityNew and with the same datatype and
content. QuantityNew can be changed with no problems.
Arne
"Sue Hoegemeier" wrote:

> Constraints don't typically hide so...let's start over.
> Could you please post what you are doing and what tool or
> application you are using? And post the exact error message
> you are receiving?
> -Sue
> On Thu, 20 Jan 2005 06:11:04 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
>
>|||The only way I can think of that you can get a message like
that is through a trigger. If it was a rule or a constraint,
you'd get a much more verbose message indicating that it
violated a check constraint or a rule, the column it was on,
the name of the constraint if it violated a check
constraint, etc.
Check for triggers on the table. In Enterprise Manager,
right click on the table, select All Tasks and then select
manage triggers.
-Sue
On Thu, 20 Jan 2005 10:45:03 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>I am inside enterprise manager. I try to change the quantity from 6000 to
>26000 in an integer column. I get a message that says the current Maximum i
s
>25,000.
>We create a new column called quantityNew and with the same datatype and
>content. QuantityNew can be changed with no problems.
>Arne
>"Sue Hoegemeier" wrote:
>|||Sue,
I finally found the offending trigger. Thanks.
Arne.
"Sue Hoegemeier" wrote:

> The only way I can think of that you can get a message like
> that is through a trigger. If it was a rule or a constraint,
> you'd get a much more verbose message indicating that it
> violated a check constraint or a rule, the column it was on,
> the name of the constraint if it violated a check
> constraint, etc.
> Check for triggers on the table. In Enterprise Manager,
> right click on the table, select All Tasks and then select
> manage triggers.
> -Sue
> On Thu, 20 Jan 2005 10:45:03 -0800, "Arne"
> <Arne@.discussions.microsoft.com> wrote:
>
>|||Glad it's fixed! Thanks for posting back the results.
-Sue
On Thu, 20 Jan 2005 13:43:09 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>I finally found the offending trigger. Thanks.
>Arne.
>"Sue Hoegemeier" wrote:
>

rtrim/ltrim works good - but need some additional feedback

Using

select rtrim(ltrim(total)) as Total

from testtable

WHERE Total is NOT NULL

The results come back allright except for a small handful:

594
1242
17458
214
6971
29023
808
1
0
37
9
65
39
9
0
0
0
2
632
9
0
641
0
2438
148
2
1882
153
556
11839
616
1250
17709
187
6630
29548
803
7880
606
1600
1479
690
765
1630
3953
2418
9
75
1563
2
635
3
175
36
783
54
28
665
60
261
11995
679
1438
17548
118
6211
29543
797
7649
572
878
488
948
273
1756
2111
1205
2
54
1068
2
188
0
58

Showing me that there are still some spaces not getting trimmed off.. I would love to present this as trimmed as possible. Is there something to this that I am missing?

this this:

select rtrim(ltrim(cast(total as varchar(100)))) as Total

from testtable

WHERE Total is NOT NULL

|||

IF all of your values are in fact numbers, then use a cast instead of all the lrtim/rtrim.

Code Snippet

DECLARE @.MyTable table
( Col1 varchar(25)
)

INSERT INTO @.MyTable VALUES (' 29023')
INSERT INTO @.MyTable VALUES ('808')
INSERT INTO @.MyTable VALUES (' 1')

SELECT cast(Col1 as int) FROM @.MyTable

--
29023
808
1

|||

They are numbers within a string that looks to be compiled of binary specs of hell.

I am not seeing hex characters, but some binary. When I use your suggestion I get:

Msg 245, Level 16, State 1, Line 2

Conversion failed when converting the varchar value '

1' to data type int.

The field value is varchar(200) and there is no way I can change without some other functions blowing up.

In the end - I have some vbscript which I will be applying to a page so that this numeric data can be summed up.

|||

Perhaps this function will 'solve' your issue -it should eliminate all non-numerical characters.

Code Snippet

IF EXISTS
( SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'fnNumbersOnly'
)
DROP FUNCTION dbo.fnNumbersOnly
GO

CREATE FUNCTION dbo.fnNumbersOnly
( @.InParam varchar(50) )
RETURNS varchar(50)
AS

BEGIN
IF patindex( '%[^0-9]%', @.InParam ) > 0
BEGIN
WHILE patindex( '%[^0-9]%', @.InParam ) > 0
BEGIN
SET @.InParam = Stuff( @.InParam, patindex( '%[^0-9]%', @.InParam), 1, '' )
END
END
RETURN @.InParam
END
GO

Then use it like this:

Code Snippet

SELECT dbo.fnNumbersOnly( Total ) FROM TestTable

|||

OMG! Yes! Too cool!

Thank you!

|||

The problem was almost certainly due to something like embedded tab characters or other non-space whitespace characters.

If you run:

SELECT ' ' + char(9) + ' 123'

you will see that the second 5 spaces after the TAB are left behind. LTrim (and probably RTrim) only seem to remove spaces.

rtrim in sql server 2000

Hi,
What is wrong with the following query?
select a.name, rtrim(a.name) from (
select top 100 name from dbo.table1 )a
whre table1 has the following in name column
A.B.xyz<space>
AB.xyz<space>
and so on...
When i cut and paste the result set from query analyzer into Excel, i
still see the trailing space.
BTW, i am using sql server 2000
Thanks in advance,
TamasWhat data type is the name column? The char/nchar/binary datatypes are of
fixed length, while varchar/nvarchar/varbinary are variable.
"Trimming spaces from fixed length columns is futile. You will be overpadded
."
ML
http://milambda.blogspot.com/|||what is the a.name datatype ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<tamashee@.yahoo.com> wrote in message
news:1145399053.665196.72630@.e56g2000cwe.googlegroups.com...
> Hi,
> What is wrong with the following query?
> select a.name, rtrim(a.name) from (
> select top 100 name from dbo.table1 )a
> whre table1 has the following in name column
> A.B.xyz<space>
> AB.xyz<space>
> and so on...
> When i cut and paste the result set from query analyzer into Excel, i
> still see the trailing space.
> BTW, i am using sql server 2000
> Thanks in advance,
> Tamas
>

rtrim function

hi,

for example; select field1 from table

field1 value=codesample

I want to change field1 value with rtrim function than field1 value must be codesam. How can I do:?

Hi,

sorry but I didn't understand your request, what do you want to achieve, could you please describe this again ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de