I need a script to find the position of the first non-numeric in a telno field and delete from that point onwards.
Example: 01208 12345 (Work) would become 01208 12345
Has anybody come across this before ?
TIA
Neil.
If I have a string with the following values, I’d like to replace the non-numeric characters with a space.
Input
01234-987
012345678
01234 ext 65656
Tel 0123456
012345 898989
Output
01234 987
012345678
01234 65656
0123456
012345 898989
I am looking for the fastest way to strip non-numeric characters from a string.
I have a user database that has a column (USER_TELNO) in which the user can drop a telephone number (for example '+31 (0)12-123 456'). An extra computed column (FORMATTED_TELNO) should contain the formatted telephone number (31012123456 in the example)
Note: the column FORMATTED_TELNO must be indexed, so the UDF in the computed column has WITH SCHEMABINDING.... I think this implicates that a CLR call won't work....
Hello,
I am from the school of thought that you should in every case have your primary keys as numeric values only. However, where I currently work there is a project leader who is a recent FoxPro convert (I know, they are tough ones to crack). I made the suggestion recently that the keys in the table should be numeric and with him being the project leader and me just a lowely developer he said get lost. I made the point that later joining your tables together in a PK/FK relationship where the keys where character would be slower then with numeric keys. He didn't listen and now we are approaching production with a database that is really just a bunch of text file. He said that with SQL 7 it doesn't matter if the pk is numeric or character. I disagree. But I need solid documentation to take to him and to the managers to convince them. If anyone out there could advise me on this. And if anyone could give me or tell me where I could find documentation on why even in SQL 7 there is a need to use numeric keys that would be a great help and you would be making one more shop in this world a little bit more technically sound :-) Thank you in advance for your help.
kc
HiI have a character field (char ot varchar) that I want to force only tocontain numeric characters.Can that be done by way of defining a constraint on the field ?or by any other way in the field/table definition ?What id the syntax ?Anyone have examples ?ThanksDavid Greenberg
View 1 Replies View RelatedIn SQL I need to be able to take a varchar parameter @Area and convert it to a float.
The input values for @Area I can't control. They can range from 6300 to 6,300 SqFt to 1.2 Acres .
So to convert this value to a float I basically look through the string and remove everything that isn't a number or a period. Then I would convert this value to square feet based on how large the number is.
Code:
DECLARE @k int, @Temp VarChar(25), @SqFt Float
SELECT@Temp = @Area
select @Temp
select @k = patindex('%[^0-9. ]%', @Temp)
while @k> 0
begin
select @Temp = replace(@Temp, substring(@Temp, @k, 1), '')
select @k= patindex('%[^0-9. ]%', @Temp)
end
If @Temp = ''
BEGIN
SET @Temp = '0'
END
SELECT @SqFt = Convert(Float, @Temp)
--Distinguish if it was acres or square feet
If (@SqFt > 750.00)
BEGIN
SET @SqFt = @SqFt
END
ELSE
BEGIN
SET @SqFt = (@SqFt * Convert(Float,43560) )
END
SELECT @SqFt
This works great except for one situation, If @Area is something like 6,300 Sq.Ft. . When I run it through the part that removes all non-numeric items and periods, I end up with 6300 .. . So to get around this I want to find the first letter in the string and then remove everything after it. Then take the result and run it through part that removes everything but the numbers and period.
However I can't find away to get the index of the alpha-numeric character and remove everything after it.
Thanks in advance!
I have one table and this field is character field with save data in below.
Bonus_table->bonus_amt_field. Char(20)
======================================
Record information
0
Null
Blank
3
4
Null
Blank
if i want to convert this character field => change to numeric field to display ,how to handle "Blank" and "null" record?
The result expect:
0
0
0
3
4
0
0
I try this query but wrong message :
select cast(convert(numeric,3)bonus_amt) as bonus_amt
from test
Hi All there -
I want to show the o/p of a cursor on a single line. There is a numeric variable that needs to be clubed with the character variable. If I use char() the o/p is not right.
How do I do that?
hi I have a table which contain an id field as a char(20)
The content of this field is combination of string and numbers as follow
id flag
--------- -----
38383
88585
18834
x4820
z4892
t9494
I need to update the flag field where first character in the id field is not numeric. HOw can I do that.
thanks for your hlep
Thanks
AL
when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric
declare @a numeric(16,4)
set @a=99362600999900.0000
The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.
Guys
I'm getting the above when trying to populate a variable. The values in question are :
@N = 21
@SumXY = -1303765191530058.2251000000
@SumXSumY = -5338556963168643.7875000000
When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)
Any ideas ??
Hi guys/ladies I'm still having some trouble formatting a select statement correctly.
I am using a sqldatasource control on an aspx page. It is connecting via odbc string to an Informix database.
Here is my select statement cut down to the most basic elements.
SELECT commentFROM informix.ipr_stucomWHERE (comment > 70)
The column "comment" contains student grades ranging from 0-100 and the letters I, EE, P, F, etc. Therefore the column is of a char type. This is a problem because I cannot run the above statement without hitting an alpha record and getting the following error
"Character to numeric conversion error"
How can I write this statement where it will work in the datasource control and have it only look at numeric values and skip the alpha values?
I have tried case with cast and isnumeric... I don't think that I have the formating correct.
I have also used:
WHERE (NOT (comment = ' I' OR comment = ' EE' OR comment = ' NG' OR comment = ' WP' OR comment = ' WF' OR comment = ' P' OR comment = ' F'))
This works but is very clunky and could possibly break if other letters are input in the future. There has to be a better way.I am sorry for my ignorance and thanks again for your help.
Hi All.
I have a coulmn that contains data in the following format.
COUNTRY/SATE/UNAME
What I would like to do is create another column in the table stripping out the COUNTRY/ and /UNAME
Example
Existing column value
US/NY/BLAH
New Column value
NY
Hey everyone!
I'm doing an export from SQL into excel spreadsheet and then am going to clean out certain parts of the data with global search/replace. The problem is that the SQL data is full of special characters such as |'s and the little box looking characters.
How do I export without these characters?
I know its possible, I did it about 2 years ago and remember I did some crazy file conversion (make wk3 or something) but I no longer remember
Any help would be much appreciated!
Thanks,
Geoff
PS, attached is a screenshot of the data to give you an idea of what I'd like to strip!
I have some sql below..
SELECT
t.Doctor, t.LedgerAmount, t.TransactionDate,
ISNULL(lg.LedgerGrpDesc, 'No Sales Group') AS LedgerGroup
FROM
Transactions t
LEFT OUTER JOIN LedgerGroups lg ON t.LedgerDescription = lg.dbLedgerDesc
[Code] .....
My problem is that the data in t.LedgerDescription sometimes now has either leading/trailing white space or more likely special chars so the join against lg.dbLedgerDesc doesn't always work.
I can't change the source of the data to strip out special chars/white space so am stuck on how to deal with it.
I tried using LTRIM & RTRIM in the where clause but this doesn't seem to have had any effect...
LEFT OUTER JOIN LedgerGroups lg ON LTRIM(RTRIM(t.LedgerDescription)) = lg.dbLedgerDesc
Environment:
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
Problem & Info:
I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls
Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
Desired Help:
How to perform
1) stripping out all undesired rows
2) importing each column into sql table
Hi There,
I'm using C# to get a value for a DOUBLE precision variable, called "Length", from a textBox using the following line:
Length = Convert.ToDouble( txtLength.Text )
I'm also using the following lines to prepare my stored procedure call:
arParms[9] = new SqlParameter("@Length", SqlDbType.Decimal, 5);
arParms[9].Value = record.Length;
My stored procedure has the following parameter definition:
@Length decimal(9,3)
My problem is that if someone types a value bigger than 999999 in the textbox he will get for sure the following error:
System.Data.SqlClient.SqlException: Error converting data type numeric to decimal.
I don't know how to either make sql or C# to truncate the value or catch the exception to automatically assign 0 to the parameter.
Please Help.
Moshe
Hi,
I have one column in which i have Alpha-numeric data like
COLUMN X
-----------------------
+91 (876) 098 6789
1-567-987-7655
.
.
.
.
so on.
I want to remove Non-numeric characters from above (space,'(',')',+,........)
i want to write something generic (suppose some function to which i pass the column)
thanks in advance,
Mandip
I need to replace Access Val() functions with similiar function in sql.
i.e. Return 123 from the statement: SELECT functionname(123mls)
Return 4.56 from the satement: SELECT functionname(4.56tonnes)
Any one with ideas please
Thanks
George
The data type in the table is decimal with a precision set to 19 and a scale set to 2 and nothing for the default value.
I need to use updates or inserts that will allow a value of nothing or null to be inserted.
UPDATE TableName SET fldAmount = '' WHERE fldIssue = 'ABC'
But this fails because of the amount field.
This statement works, but is not good for me because 0 is not accurate.
UPDATE TableName SET fldAmount = '0' WHERE fldIssue = 'ABC'
How can I insert nothing?
Thanks
I Have a table with #Sample like below
=================================
#Sample
id int,
SSN varchar(20),
State varchar(2)
Sample Data:
ID SSN STATE
1 999-000-000 AB
2 979-000-000 BC
3 995-000-000 CD
=================================
We used filter logic based on the SSN & State.
We are passing these values through variables like
Declare @State varchar(2)
Declare @SSN varchar(20)
While run time these values are lets suppose @SSN = '999-000-000' & @State='ABC'
Now the Result is displayed with the state data Like 'AB' only.
Output: 1 999-000-000 AB
instead it should give system generated error.
Here I have 2 Questions:
1. Why it is taking 1st 2 Charecters?
2. Why it does not have any system generated for length?
I can do validation with Length function for these 2 variables however if have 100 variables then it should not feasible case. So, what is the reason behind?
I'm using a DTS package to import a large CSV file. There is a particular column that contains text or numbers. I want to delete the row if that column has a number, I've used IsNumeric in the selection portion of the statement, but can't figure out how to use it as part of my where clause.
View 1 Replies View Relatedusing this syntax numeric() can someone pls help me with the syntax to create a numeric column with a scale of 6 thanks
View 2 Replies View Relatedhow to write a query to know that a column contains text or numeric data using a select sql statement.
is there any function for this???
Please help
Hi,
I have a lot of decimals in the flat files. So far the largest numbers map to a numeric(18,6).
My question is, is DT_NUMERIC the correct datatype for this data? In which case, what size do I need to set it? Right now it's 18. Couldn't find much info on this.
Thanks
In MS Access, for numeric fields, the decimal places shown can be defined as "Auto" meaning that the database will determine the number of decimal places to show based on the content of the field (i.e. 1.0, 0.75, 1.125).
In SQL Server for the same field, it appears that decimal precision is hard coded resulting in a fixed representation (i.e. 1.000, 0.750, 1.125)
Is there a way to make the decimal representation in SQL Server more like Access where trailing zeros are truncated?
I am trying to insert some values into a table where the column is of the data type "numeric". The insert works fine.Update does not work.
Update BUT_BREAKDOWN_PCT SET BDP_EFFORT_BREAKDOWN_PCT=0.15 WHERE BDP_BREAKDOWN_ID =1 AND BDP_PHASE_ID = 3 AND BDP_START_EFF_DT = '12/31/2004'
BDP_EFFORT_BREAKDOWN_PCT is a numeric column with a size 5 (4,3)
When I do the updatedirectly from QA, it works fine.
I was googling it and read a KB article saying it's a problem with Service Pack of SQL Server 2000. If it is, then the query should not work even from QA....isn't it?
Anyone had this problem before? Please help.
Whats the difference between int,float and decimal in sql server.
WHich data type takes how many bytes?
What data type i should use to store the following sets of data
1set----100000,854275,74892734
2set----6538726.98765923,762.659325
Thanks.
Hi
I'm using an SQL server database to run a website with ASP.
A new page I'm making will only display the contents on fields containing numeric data and it won't display text.
Other pages work fine. Does anyone have any ideas as to why this is happening - I've never seen in before.
Dave
http://pink-football.com/gossip.asp
Hello,I need to be able to select only the numeric data from a string that isin the form of iFuturePriceID=N'4194582'I have the following code working to remove all the non-numeric textfrom before the numbers, but it is still leaving the single quote afterthe numbers, i.e. 4194582'Any ideas or suggestions how to accomplish that? Thanks in advance.Declare @TestData varchar(29)Set @TestData = "iFuturePriceID=N'4194582'"Select Substring(@TestData, patindex('%[0-9]%', @TestData),Len(@TestData))TGru*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies View RelatedI have some data which I am trying to put into a DM where I can use it as part of a cube (my first!!)
I have hit a small problem with dates, I get it from the ERP system as a numeric field, and I need to convert it to a date format. The intension is to use this converted data with Named Calculations to derive Year, month Day ect.
However I cannot seem to be able to convert and store (in SQL) this column can anyone advise
Thanks
Hi
i am using SQL SERVER CE database. I have many tables having numeric fields.
When i am inserting data through frontend all the float data is getting rounded.
I want all data in float with point . How can i do that??????????
Thanx in advance
I'm getting some data from a flat file with a SSIS Package, it comes a integer but I would like to converted to a decimal with a 3 scale.
Example:
Flat File: 2070015000950011800
In the data conversion I had it with a 3 scale, but what I got was this:20700.00015000.0009500.00011800.000But what I want is something like this:20.70015.0009.50011.800
I dont know if you guys get the idea. But I will apreciate if anyone can help me.
Thanks,
Erick