hi , can anyone tell me if there exists a function in SQL Server that help me determine if a variable is ALL Alphanumeric data or not by returning a 0 or 1 when condition fails or succeeds . thank you
In 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.
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.
We have a table with an indesx that is varchar(7). It contains up to 5 numbers and 2 characters to denote batches. IE. 100a 105zz 1c 2 10001w
In an Access gui I need to be able to sort this field numericaly so each number shows up in its correct position. IE. 1c 2 100a 105zz 10001w
In order to accomplish this, I created a seperate field on the table named Num as varchar(5). I would like to create a trigger that updates this field any time a new batch number is entered. This way I can have my queries order by Num when returning the recordsets to the GUI.
Below is the code that I thought would work, but it is giving me an error near the keyword BEGIN after the IF(ISNUMERIC) statement. I can't seem to find an error with this code, but I thought maybe a thousand fresh sets of eyes could. Any help would be greatly appreciated. Also if you know of a better way to accomplish this task, please feel free to post here.
TIA,
Aaron
CREATE TRIGGER [InsertNum] ON [dbo].[IP_Batch_Table_Temp] FOR INSERT, UPDATE AS
DECLARE @Num varchar(7) DECLARE @Num1 CHAR(7) DECLARE @x INT
SET @Num = (SELECT IP_Batch_Number FROM inserted) SET @x = 1 WHILE (@x < len(@Num)) BEGIN IF (ISNUMERIC( SUBSTRING(@Num, @x, 1) ) ) BEGIN SET @Num1 = @Num1 + SUBSTRING(@Num, @x, 1) END SET @x = @x + 1 END UPDATE IP_BATCH_TABLE_TEMP SET NUMBER = ltrim(rtrim(@Num1)) WHERE IP_BATCH_NUMBER = @Num
If I create an index on a field in SQL Server, what will be the most efficient (fastest) field type to index a field? (This field will be a "Pointer" to a child table that will contain a list of codes, and their description.) Would a Numeric field be quicker than a VarChar field? VarChar would make it easier for a Human to decipher the raw records. (For example, if I used a numeric the code would be 42 or 47, while the VarChar could be 'savings' or 'checking'.) Basically I will have the following "Master" table: FieldType --------- IDInt NameVarChar StatusInt -or- VarChar Customer_TypeInt -or- VarChar If Customer_Type is a code that can be looked up in another table, and I index that field, would I want the "Code" to be an Int or VarChar? SQL: Select * From Master Where Customer_Type = <42> or <'savings'> My Where clause would depend on the field type. Thank you, Bryan
HI, Thanks in advance for taking your time to read this post. I am trying to write a SQL query using MS SQL 2005 that will read the value of a field and tell if it is alpha or numeric. I have tried the following but it does not work: select field1 from table1 where left(field1,2)='[0-9]' select field1 from table1 where isnumber(left(field1,2) tried with a =1 at the end and without and =1 at the end the goal is to read through a field and format it so if a field looks like this 12xxx111xx I can change it to look like 12-xxx-111-xx. Any help is greatly apprecaited
Hi, I have a little bit of a problem I cannot seem to figure it out. Is it possible to write a Select statement that contains a WHERE column_name > desired_numeric_value The tricky part it that the column is of CHAR type and can contain numeric grades ranging from 0-100 or the letter I for Incomplete. My SQL was working perfect when this column contained only numbers as soon as a record with I was added I get the following error: Character to numeric conversion error This report will be used to find students who have failing grades. Thanks for any help!
Karikalan writes "We need alpha numeric auto increment code in sql server 2000. (for eg.: ico1001, ico1002, ico1003,......) Can any one send the code in MS sql server 2000? plz .................. bcoz i am beginner in sqlserver 2000"
SET @Counter = 1 SET @Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)
WHILE @Counter <@Max BEGIN SET @MATCH_Supplier_name = (SELECT Match_Supplier_Name FROM SUPPLIER_TABLE_TEST WHERE @Counter = DTect_Supplier_SRN) SET @Index = LEN(@MATCH_Supplier_name) WHILE @Index > = 1 SET @MATCH_Supplier_name = CASE WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[a-zA-Z]' TH EN SUBSTRING(@MATCH_Supplier_name, @Index, 1) WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[0-9]' THEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) ELSE '' END + @MATCH_Supplier_name SET @Index = @Index - 1 --PRINT @MATCH_Supplier_name SET @Counter = @Counter + 1 END
As part of a data search project I need to be able to strip all non numeric characters from a text field. The field contains various forms of phone number in various formats. In order to search on it I am going to remove all non numeric characters from the input criteria and from the data being searched.
In order to do this I decided on using a SQL Server custom function: Pass in field. Loop through all chars, test against asci values for number range. return only numernic data concatenated into a string.
Are there any other more efficient ways of going about this?
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....
Hi to all, I am having a string like (234) 522-4342. i have to remove the non numeric characters from the above string. Please help me in this regards. Thanks in advance. M.ArulMani
Hi to all, I am having a string like (234) 522-4342. i have to remove the non numeric characters from the above string. Please help me in this regards. Thanks in advance. M.ArulMani
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.
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)
I am unfamiliar with the built-in functions in SQL Server. I would like to display a numeric field with leading zeros. In oracle the sql stmt would be:
I'm attempting to move data from a text field to a numeric field, but I need to be able to remove any non-numeric characters in that field first, but I do not want to lose the numeric data.
In the code below, the Value table is the table that will be converted.
INSERT INTO IntegerValues (FK_ReferenceID,FK_ReferenceType,FK_FieldID,FieldProperty,Value,CreationDate,CreatedBy,Revision) SELECT FK_ReferenceID,FK_ReferenceType,FK_FieldID,FieldProperty,Value,CreationDate,CreatedBy,Revision FROM StringValues WHERE FK_FieldID in (select id from ##tmpFields)
Our company needs to (restore/migrate) our Alpha Nt4sp4 sql 6.5sp5a databases to an Intel hardware platform. For the time being the new intel server will be runing Nt4sp4 sql 6.5 sp5a. I understand that if we were to upgrade to sql 7 we would be able to restore the databases without any problems. However upgrading to sql 7 is not an option right now. I have tried to restore a backup from the alpha to an intel based machine, and it fails because the processor types are different.
I have already search technet, but found nothing, except upgrade to sql 7.
I am hoping someone out there could point me in the right direction.
Thank inadvance for your help, I appreciate it very much.
For the last couple of days i've been trying to transfer a SQL6.5 database from an Alpha platform to an Intel one.
At one point i loaded SQL7 on the Intel platform and tried to perform an upgrade (using the wizard), across the network - but it just did not want to play.
Using Veritas Backup Exec, with SQL6.5 on both machines, i've tried doing a backup of the database on the Alpha platform and then restore to the Intel Platform, but this fails stating that the processors are incompatible
Am i trying something that is impossible?
If anyone could point me in the right direction i'd be truely grateful.
I am looking for ways to migrate Sqlserver 6.5 from Alpha to Intel plat form. The only method I heard was Database/Object transfer. Is there any other way?
I'm trying to move a database over from an Alpha to an Intel-base PC. The SQL Server database is on an Alpha server. The 2nd machine is an Intel. When I make a backup of the database from Alpha, copy over the file to the 2nd PC (Intel) and then try to restore it from the 2nd PC, it doesn't sure up on the list of restores when I click on 'Add Device'
The only other way I can get this to work successfully is with a Database Object Transfer, which takes too long (several hours) and limits me to being on site. I need to do this every few weeks so I need a better way to transfer the database over ?
Anyone have any experience with SQL Server on Alpha??
Hi all, I defined an user string type varible in the package as AccountLen. I am trying to use this varible in the Expression of Derived Column transformation. I want to retrieve a part of column, i.e: Right(Column1, @AccountLen), this is always wrong because the AccountLen is string type. How I can convert it to the numeric so that can be used in the RIGHT function? Thanks
