Apply Substring On Decimal

Feb 11, 2008

Hi,
I have data about amount which is Decimal in my Excel file . The data is like this: $100.24. So In my SQLServer, I declared Amount column as Decimal. Before doing the exporting this data into SQLServer table, I need to remove $ symbol from the amounts. I applied like this:
Substring([Amount], 1, Length([Amount]-1). It is giving error : Incorrect format or data invalid when I entered this expression in Derived column. How to remove $ sign and store the remaining part from the Amount field?
Thanks in advance

View 3 Replies

CROSS APPLY Vs OUTER APPLY Example Messed Up?

Nov 27, 2007

Hi... I'm reading the MS Press 70-442 Self-Paced Training kit, and I'm having problems with this example.
I'd like help getting it to work correctly, or understand why it is isn't working the way I planned.

On page 67, the lab is about the APPLY operator (CROSS APPLY and OUTER APPLY). I first have to input a sample table-valued function into the AdventureWorks database:

Code Block
CREATE FUNCTION fnGetAvgCost(@ProdID int)
RETURNS @RetTable TABLE (AvgCost money)
AS
BEGIN
WITH Product(stdcost)
AS
(
SELECT avg(standardcost) as AvgCost
FROM Production.ProductCostHistory
WHERE ProductID = @ProdID
)
INSERT INTO @RetTable
SELECT * FROM Product
RETURN
END

and then run a sample T-SQL statement

Code Block
SELECT p.Name, p.ProductNumber,
Convert(varchar, cost.AvgCost,1) AS 'Average Cost'
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) AS cost
WHERE cost.AvgCost IS NOT NULL
ORDER BY cost.AvgCost desc

My problem is with the WHERE clause... According to page 56, CROSS APPLY returns only rows from the outer table that produces a result set, so why do I need to explicitly filter NULL values?

When I remove the WHERE clause, the query retrieves lots of NULL AvgCost values.

Again, according to page 56, it is the OUTER APPLY that returns all rows that return a result set and will include NULL values in the columns that are returned from the table-valued function.

So, in short, I don't see the difference between CROSS APPLY and OUTER APPLY, using this example, when I remove the WHERE clause?

(Please refrain from introducing another example into this question.)

View 8 Replies View Related

Legacy Database Uses Decimal Data Types.--&> AutomobileTypeId (PK, Decimal(10,0), Not Null) Why Not Integers Instead ?

Sep 26, 2007

I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?

Example: AutomobileTypeId (PK, decimal(10,0), not null)

View 5 Replies View Related

Data Type With Decimal Point For Decimal Values But Not For Whole Integers

Dec 8, 2013

I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?

View 2 Replies View Related

Cast Or Convert Nvarchar With Comma As Decimal Separator To Decimal

Apr 29, 2008

Hello.

My database stores the decimals in Spanish format; "," (comma) as decimal separator.

I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.

Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:

Error converting data type varchar to numeric

Any knows how to resolve.

Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.

View 5 Replies View Related

Converting (casting) From Decimal(24,4) To Decimal(21,4) Data Type Problem

Jul 24, 2006

Hello!

I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?

Thanks for giving me any advice,

Ziga

View 6 Replies View Related

How Can I Use The Decimal Comma Instead Of Decimal Point For Numbers In Jet Engine?

Sep 19, 2007

I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl

View 4 Replies View Related

Converting Decimal To String W/O Decimal Point

Jul 23, 2005

I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?

View 6 Replies View Related

Converting Decimal Point To Decimal Comma

Nov 30, 2007

Hi all,

I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.

e.g.
��1,500,123.00 to ��1.500.123,00

Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.

Any help would be much appreciated

Thanks!

View 5 Replies View Related

Decimal Limited To 4 Digits To Right Of Decimal?

Jun 18, 2007

I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.

I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.

It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).

Is the decimal limited to scale 4 ???

Thank you for your help!

Anna

View 3 Replies View Related

Packed Decimal To Decimal Conversion

Jun 4, 2007

Hi,

I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.

File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.

Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030 œ&
20060715 0001010100010101

Please help!

Thanks

Mirudhu

View 4 Replies View Related

How To Apply Hot Fix?

Mar 26, 2007

I have a sql server problem for which the solution is at,

http://support.microsoft.com/kb/889521

The article talks about a hot fix, but there is no link for download the hot fix.

Can anyone have a look and guide me.

Thanks

------------------------
I think, therefore I am - Rene Descartes

View 4 Replies View Related

Substring

Jul 5, 2001

Hi all,
I have just started using SQL7 and quite dumb at it.
Here is my problem

i have tables ip_address and ip_subnets. both contains more than 20,000 records. Though ideally each subnet should correspond to only one ip address it is not so due to SMS inventory and remote clients configurations etc.

As an example If my ip address is 141.151.128.78 I need to select only 141.151.128.64 ( or atleast 141.151.128.*)as the valid subnet. In other words I need to compare upto 3rd octet and only if it matches with ip address then declare that as the valid subnet.
Pls note that ip addresses vary for each machine though there will 4 octets, I can't use character positions as the nos in each octet might vary from 1-255.

Any help would be greatly appreciated
Pasted here under is the query script I am playing around with charcter poistions which are not working in my favor. Just added to explain my problems in more clearer way
select distinct system_data.name0, System_IP_Address_ARR.ip_addresses0, System_IP_subnets_Arr.ip_subnets0, system_disc.client0 from system_Data, System_IP_Address_ARR, system_ip_subnets_arr, system_disc where system_data.machineid = System_IP_Address_ARR.itemkey and system_data.machineid = system_ip_subnets_arr.itemkey and system_data.machineid = system_disc.itemkey and system_disc.client0 = 1 and substring(System_IP_Address_ARR.ip_addresses0,1,10 ) = substring(System_IP_subnets_ARR.ip_subnets0,1,10) and substring(System_IP_Address_ARR.ip_addresses0,10,1 ) = '.' and System_IP_subnets_ARR.ip_subnets0 not in ('11%.%.%.%','12%.%.%.%', '10%.%.%.%' , '10.%.%.%' , '1.%.%.%') order by System_DATA.name0

Arun

View 2 Replies View Related

Need Help On Substring

Nov 5, 2001

Hey you sql programming guru's

I need help seperating a name from first name and last name

The field is like this,

last name, first name
example Doe, John
I need to seperate the last name from the , to the first character
and the last name from the , to the last character.

I think I have to use a substring but not sure how tell it to stop and
start when it gets to the comma.

Can someone please help me.

Thanks,
Dianne

View 1 Replies View Related

SubString

Aug 8, 2001

Need to do something like SUBSSTRING in Access, any ideas?

View 1 Replies View Related

Using Substring

Oct 4, 2004

Hi

Can anyone show me how to do the following:

I have this snippet of data

ids
-----------------------------------
1582270|1582277
1582270|1582277|1582286
1582270

I want to return the id (the id may not always have the same number of numbers) after the last pipe (|) delimter.

So i want a rs like this :
ids
-----------------------------------
1582277
1582286
1582270

Thanks in advance

View 9 Replies View Related

Substring Help

Feb 4, 2005

Hi,
please help me in developing query to satisfy this...
i have table called test and the table data looks like this.

col1
123.abc
1.ab
12.cba

the query needs to return all rows data after . the result set should return this

abc
ab
cba

appreciate your help..
thanks
sskris

View 1 Replies View Related

Substring

Feb 3, 2004

I have a field that contaings 15 characters. I want to just pull the first 6. So anything that matches these 6 will be returned.

View 10 Replies View Related

Substring???

Apr 8, 2004

We have entries like below in a table. I need a query by which I can get the CustomerName from the below entries.

ACustomerName
ACustomerNameCredit
ACustomerNameDebit

Thanks!

View 14 Replies View Related

Substring

Jun 2, 2004

I an trying gto devide this one field that contains city state and zip into 3 seperate columns. The Column right now looks like this:

Coulumn1
-------------------------------
SOUTH EL MONTE CA91733617
BOSSIER LA71172
GARDENA CA90249107
MILWAUKEE WI53216
PARIS IL61944
DUQUOIN IL62832
REDWOOD FALLS MN56283
AUBURN ME04210

I tryed this:

use cimpro1
select substring(cust_shipto_addr_l3, 1, 19) as 'City',
substring(cust_shipto_addr_l3, 20, 21) as 'State',
substring (cust_shipto_addr_l3, 22, 31) as 'Zip'
from opcshto

For some reason, when I run the query I get this for State:

State
-------------------------
CA91733617
LA71172
CA90249107
WI53216
IL61944
IL62832
MN56283
ME04210

When I use the substring to only pull characters 20 and 21 it pulls everything startign at 20. I just want it to select character position 20 ans 21 for the state. As far as the substring for City and Zip, everything comes out fine. Its just State that I am having trouble with.

Any help is appreciated.

View 7 Replies View Related

Substring

Sep 13, 2006

iam trying to write a string function which will give me the id part of a mail id
but iam geting the string along with @ and when iam trying to remove the last char (@) iam getting error

query:
select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail)))
from lead_details -----> Gives me id along with @

select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail) - 1))
from lead_details ------------>gives me error "Invalid length parameter passed to the substring function."

But
select (CHARINDEX('@', leadassignedtombemail) - 1) from lead_details
works and gives me the length of id without counting @

where did i go wrong

View 2 Replies View Related

Help With Substring

Dec 1, 2006

i am trying to get the last name of the customer, but my db has the names stored as (first,middle, last) order in a single field. i am using the statment:
,RIGHT(ActCustName,LEN(ActCustName) - CHARINDEX(' ',ActCustName) ) AS LAST

but it only works if the customer does not have a middle name, otherwise it returs the middle+last as the last name. what should i do/ any ideas??? here is my code

select ActPrjMgr
,ActEmpId
,ActEmpName
,ActCustName
,RIGHT(ActCustName,LEN(ActCustName) - CHARINDEX(' ',ActCustName) ) AS LAST
,ActPrjCode
,left(ActPrjType,2) as Status
,ActEmpTaskCode
,left(ActBillingPeriod,11)as ppedate
,left(ActivityDate,11) as actdate
,ActTimevalue
from dbo.ACTIVITIES
where ActBudCat = 'labor'
and ActBillingPeriod = '11/17/2006'
and actprjcode <> ' '
and actprjcode is not null
--and ActBillingPeriod = @StartDate
order by ActPrjMgr
,ActEmpID
,ActEmpTaskCode
,ActivityDate

View 7 Replies View Related

Substring

Mar 19, 2007

Hi All

Iam new to sql, Iam using sql 2000 and have a field datatype varchar.

An example of the string: -

CN=Leighton Morgan/OU=WR/O=Extranet

I need to write a query that will allow me to extract the following:-

Name: Leighton Morgan
Company: WR
and lastly the word Extranet

Please can you help

Many Thanks

View 9 Replies View Related

Substring

Mar 19, 2007

View 1 Replies View Related

Substring

Mar 19, 2007

View 1 Replies View Related

Substring

Jan 25, 2006

i have a column that has "Full name" of an individual.

I want to create a new column with just the first name.

How do i populate this column with just the first name...ie it just takes the string until the space from the "full name" column.

View 5 Replies View Related

Substring

Oct 22, 2007

can anyone help me with the following report.

Create procedure Rpt_StaffDevelopmentReport
(@cmb1 as varchar(100),
@cmb2 as varchar(100) with encryption as

Begin
--@cmb1 is the site name (s.sitename), @cmb2 is the staffname in the form ��surname,forename��

--@cmb2 will need to be split based on the ��,�� within the string
--tables needed are staffDevelopment , staffMember and Site
end

Three Tables.

Site
Columns
Siteid, sitename, address1, address2

Staffmember
Staffed, surname, forename,sex, address1, address2, siteid

Staffdevelopment
Staffed, development, personal

View 7 Replies View Related

Does SETROWCOUNT 10 Apply To Only The Sp In Which It Used Or Outside It Also?

May 23, 2007

I am using SETROWCOUNT 10 in my stored procedure. At end of thi sp I use SETROWCOUNT 0.
Will all other sp's that are executing at the same time as the above sp, get affected by the above SETROWCOUNT statement?

View 1 Replies View Related

Using Like And Apply To 3 Columns

Sep 7, 2007

I have a table called Courses with columns "courseCode" "courseName" and "TeacherName"Im passing in a value @prefixText ...I have the following...Select courseModuleCode from courses Where courseModuleCode like @prefixTextwhat I actually want to do is return one list, i.e. one column that will do the same for "courseName" and "TeacherName" aswell based on what the @prefix tag is...so i will end up with one column that may contains some or all of the following courseModuleCode, courseName, TeacherName. Im basically implementing a search on this table and want the users to search by any of the column nameshope this makes sense.

View 6 Replies View Related

DTS Globalvariable Not Apply ......

Jun 24, 2004

I am new to DTS.
In ExecuteSQLTask:
want the original sql statement of <<backup database databaeABC to backup_databaseABC with init>> to be

backup database ? to 'backup_' + ? with init

But it looks the parameter could not place a globalVariable to it and parse it with error.
One alternate way probably need to create a proc to pass a parm to it....

Also, how could pass a globalVariable to a FTP task?

thanks
-D

View 4 Replies View Related

Best Way To Apply SP3 In Replication

Aug 27, 2004

We have a Pub, Dist and Sub Server all running under SP2 with worm update and are looking to apply SP3a . Replication is transactional with heavy usage . What is the best sequence to apply SP3

can it be spread out in 3 weekends or has to be done all together ?

Thanks

View 5 Replies View Related

Apply Lock On A Row?

Sep 12, 2013

I want to apply lock on a row, where by when another application is trying to access the locked row the application will automatically move to the next row instead of waiting for the locked row

View 3 Replies View Related

Sp3a Does Not Seem To Apply

Feb 26, 2004

I am trying to install sql server sp3a. I checked the version before and after the sp is applied, and it shows:

8.00.194

Now, according to the sp3a readme:

http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp

the following versions should show:

SQL Server 2000 Original Release 8.00.194 RTM
Database Components SP1 or Desktop Engine SP1 8.00.384 SP1
Database Components SP2 or Desktop Engine SP2 8.00.534 SP2
Database Components SP3 or SP3a, or Desktop Engine SP3 or SP3a 8.00.760 SP3

This would imply that after installing sp3a and restarting, the version reported should now be 8.00.760. However, it still shows 8.00.194. So it appears to have not really installed, althought the sp3a installation ran without incident. So, how can I get the sp3a to not just act like it installed, but actually get it to install? Also, I have shut down all mssql services and rebooted, applied patch, and rebooted again. Doesn't help.
Also, if it is really installing, is there another way to tell?

Anyway, I really need to be able to install sp3a to see if it fixes another problem I'm having. Unfortunetly, since I can't even tell if the sp3a is installing, I can't tell if it really doesn't help or not. Thanks.

Tony F

View 2 Replies View Related