Check The First Occurence Of A Numeric In A String

Mar 17, 2008



Hi All,
I want to extract a numeric value from a string. Example, in a string like - Mgmt Pack: Processor Exception Threshold >80% Every 10 Minutes. - I want to extract that number 80. Since, later I'll want this number to plot a graph.

Since this is going to be an alert pulled from OnePoint (MOM Operational Database), the number will vary, and so I cannot look for the same number. So, can anyone help me how to get this working?

Thanks a lot in advance and let me know if the question is not clear.

Manoj Deshpande.

View 4 Replies


ADVERTISEMENT

String Occurence

Dec 2, 2003

Hi all,

Is there a function in SQL that will return the number of occurence of 1 string to the other.

e.g.

string1 = "1"
string2 = "2131145"

function should return 3

How do i obtain this in SQL?

Thanks in advance

View 9 Replies View Related

Counting The Occurence Of A String ...

Aug 27, 2006

Hi ...I have a weblog database where I want to count the occurences of atable of string values that appear in all the urls viewed.My tblWebLog as a field that contains the url ...tblWebLog.[cs-uri-stem]I have another table ... tblStrings ... that has a field [strSearch]for a string value and an integer field [intViewCount] to count theoccurence of the string in tblWebLog.[cs-uri-stem]I've been trying ...Update tblStringsSet [intViewCount] = (Select Count(*) From tblWebLog Where[cs-uri-stem] Like '%_' + tblStrings.[strSearch] + '.htm%').... but it doesn't fly and I'm stumped. Any thoughts?Cheers.

View 2 Replies View Related

Finding The Character Position Of Nth Occurence In A String.

Jun 15, 2001

Hi Friends:

I usually run this query in Oracle to find out the position of a character in a string from first position to the nth occurence.

For example, I run the following queries in oracle to get the desire result.

SQL> select instr('DADADAQQQA','A',1,1) FROM DUAL;

INSTR('DADADAQQQA','A',1,1) -- First occurence of 'A' from start.
---------------------------
2

SQL> select instr('DADADAQQQA','A',1,2) FROM DUAL;

INSTR('DADADAQQQA','A',1,2) -- Second occurence of 'A' from start.
---------------------------
4

SQL> select instr('DADADAQQQA','A',1,3) FROM DUAL;

INSTR('DADADAQQQA','A',1,3) -- Third occurence of 'A' from start.
---------------------------
6

SQL> select instr('DADADAQQQA','A',1,4) FROM DUAL;

INSTR('DADADAQQQA','A',1,4) -- Forth occurence of 'A' from start.
---------------------------
10


Is there ay equivelant way in Transact- SQL? If not, can anybody suggest the solution?


--Raj

View 4 Replies View Related

Built In Function To Search The Occurence Of Char In String

Jan 10, 2008



Hi Everyone-
i woder if someone know Built in function to search the occurence of char in string in T-SQL

and iam not talking about the Index of the first occurence (e.g CHARINDEX )
but it is more generic

i want to send to the function to ask about the index the second or the third or N occurence of the char.
and i wonder if it is built in for performance reasons

example
getseachIndex("M|d|d","|",2)==> the result is 4

View 3 Replies View Related

Removing Non-numeric Characters From Alpha-numeric String

Oct 24, 2007

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

View 18 Replies View Related

Pattern Matching - Searching For Numeric Or Alpha Or Alpha-Numeric Characters In A String

Aug 18, 2006

Hi,

I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.





Wildcard
Meaning



%


Any string of zero or more characters.



_


Any single character.



[ ]


Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).






Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).

Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

using single quotes and the % wildcard instead of Access' double quotes and * wildcard.

Just putting this out there for anybody else that is new to SQL, like me.

Regards,

Patrick Briggs,
Pasadena, CA






View 1 Replies View Related

How To Check If A Column Is Numeric Or Not

Jul 12, 2007

 I want to fin out the number of records that are not numeric on my database, how can I fin that using an SQL query?

View 2 Replies View Related

How To Check For A Numeric Value In A Column

May 20, 2004

Hello All,

Can someone pls help me with how to check for a numeric value in a varchar column?

For example I have a column called client_id , it has values "AB" , "CD" , "18", "19" . I need to delete those client_id where the values are 18 and 19. How would I do that?

Thanks in advance!!

View 2 Replies View Related

How To Check Numeric In Derived Column?

Mar 15, 2007

I would like to know how can i use derived column to covert the scrip that use to transformation column in DTS 2000(i've migrate my dts to ssis) i didn't know what function can check the numeric in ssis.

My scrip in transformation in dts 2000 as follow.
Function Main()
if isnumeric(DTSSource("Col012")) then
DTSDestination("SH") = int( DTSSource("Col012"))
else
DTSDestination("SH") = DTSSource("Col012")
end if
Main = DTSTransformStat_OK
End Function


Any suggestion please let's me know.

Thanks.

Best Regards,

Kus

View 5 Replies View Related

Skip Alphanumeric Values (or Check If Value Is Numeric)

Dec 18, 2007

Is it possible to write a sql statement to skip aplpha numeric values? I got a field containing these values; 20, 70, 150, 140, 100, KORT, 90, 180, 160. And I'm trying to check if any value is bigger than 175 (@Limit), but I want to skip the value 'KORT'. So is it possible to check if a value is numeric or not? ISNULL( CONVERT(int, ProductVariant.Size), 0) > @Limit  Regards, Sigurd 

View 2 Replies View Related

How To Check Numeric Values In A Varchar Field

Feb 11, 2003

Hi all,

I have a field defined as varchar(8) but this field should not contain any letters, needs to be only numbers. How can I validate the data if it contains only numbers? Any ideas?

Thanks,
Jannat.

View 5 Replies View Related

Check Numeric Data Type In SSIS

Sep 3, 2007

Dear All,

the situation is that i have a column data comes from flat file and all i want to do is to check that the incoming column is numeric(12,3) and if the incoming data exceed that size "12,3" exception or redirect the row is happened.



the problem that i try to apply that with the data conversion or Derived column component but it in case of the scale of the incoming data exceed 3 the component trim until 3 scale.



i also try to perform it with the flat file data source component but i face a problem that if the data in the column is empty then flat file data source component read the numeric column as Zero



i hope someone help me coz i need to handle it soon.


best wishes
Maylo

View 5 Replies View Related

Help: About Ms Sql Query, How Can I Check If A Part String Exists In A String?

May 22, 2007

Hello to all,
I have a problem with ms sql query. I hope that somebody can help me. 
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)).  Example Datas for Table Relationships:                               IDMember     Relationships              .
                                                                                                                3387            (2345, 2388,4567,....)
                                                                                                                4567           (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query: 
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
 
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
 
Best Regards
Pinsha

View 9 Replies View Related

Fetch Numeric Value From String?

Apr 1, 2015

I want the Numeric Value from Combination of alphabets(a-z,A-Z) , Special Characters and Numeric Value.

example ::

I have '13$23%as25_*' and query should return --> 132325 as result.

View 7 Replies View Related

Convert String To Numeric

Jul 29, 2007



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

View 3 Replies View Related

String Operations On Numeric Value And ODBC

Apr 10, 2001

This statement works fine in T-SQL, but when executed thru ODBC results in a <NULL>. How can I get this to work thru ODBC....? Im guessing I need to use CAST, but not sure how the sytax would be, my attempts have worked but still resulted in a <NULL>...

UPDATE mytable SET
switch = left(switch, 3) + '1' + right(switch , len(switch) -4)
WHERE blah = blah


(switch is a Numeric field)

Thanks,Adrian

View 1 Replies View Related

Performance Difference Of Numeric And String

Sep 15, 2014

I have one question what is performance difference between cluster index on numeric field or string field? I know that numeric is faster but why it is faster?

View 1 Replies View Related

Parse A Numeric String From A Field

Jul 23, 2005

Hello All,I'm trying to parse for a numeric string from a column in a table. WhatI'm looking for is a numeric string of a fixed length of 8.The column is a comments field and can contain the numeric string inany positionHere's an example of the values in the column1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-982) wed.kx10/26 Netrez 95860536Now I need to parse through these lines and return only the 8 digitnumbers in itThe result set should be2706893595860536This is what I've done so farDeclare @tmp table(Comments_Txt varchar(255))Insert into @tmpselect Comments_Txt from Reservationselect * FROM @tmp where Comments_Txtlike ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')But it returns the entire comments field in the result set. What I needis a way to return just those 8 digits.Any Ideas??Thanks in advance!!!

View 2 Replies View Related

Selecting Only Numeric Data From A String

Jan 12, 2006

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 Related

Stripping Non-Numeric Characters From A String Function

Jul 9, 2002

Hi,

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?

View 4 Replies View Related

Excluding Character From A Numeric String X34820

Sep 13, 2001

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

View 2 Replies View Related

SQL Server 2008 :: Get Numeric Values From String

Jul 29, 2015

Why can i get the numeric values from this string?

{_cpn}=1743; {_cpnll}=4511

Result: 1743, 4511

Position and len of the value can be different...

View 6 Replies View Related

How Can I Conserve The Initial Zero When Convert Numeric To String Using STR()

May 28, 2006

Sorry to raise a stupid question but I tried many methods which didwork.how can I conserve the initial zero when I try to convert STR(06) intostring in SQL statment?It always gives me 6 instead of 06.Thanks a lot.

View 15 Replies View Related

Transact SQL :: Strip Non-numeric Characters From String

Jul 27, 2015

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

View 20 Replies View Related

Return Only Numeric Characters From A String Column

Dec 4, 2007

I have written this query to return only numeric characters from a string.

select *
from TableA
where isNumeric(Column_A) = 1

But I have discovered that the following

SELECT IsNumeric('-')
SELECT IsNumeric('£')
SELECT IsNumeric('$')
SELECT IsNumeric('+')

all return a value of 1. I do not want these in my result set.

If I use Column_A NOT LIKE '%[a-z]%' and Column_A <> '' I get characters such as "", ----- etc in my result set.

I would like a simple way of only returning numbers in my resultset.

Thanks for looking at this.

View 2 Replies View Related

SQL Server 2012 :: Strip Non-numeric Characters From A String

Jul 15, 2015

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....

View 9 Replies View Related

Sql2k: Increment Numeric Part In Arbitrary String

Apr 11, 2003

CREATE FUNCTION fctisnumericex(@c varchar(1))
RETURNS int AS
BEGIN
RETURN CASE WHEN ASCII(@c)>=ASCII('0') AND ASCII(@c)<=ASCII('9') THEN 1 ELSE 0 END END

CREATE FUNCTION fctstringincrement (@string varchar(255),@maxlen int)
RETURNS varchar(255) AS
BEGIN
DECLARE @@posr int
DECLARE @@posl int
DECLARE @@c varchar(1)
DECLARE @@token1 varchar(255)
DECLARE @@token varchar(255)
DECLARE @@token3 varchar(255)
DECLARE @@i int
/* emulates parts of the behaviour of s_modformatting::substringincrement */
/* 1. find the place where the numeric token starts from the right */
/* if we didn't find any non-numeric part then it might well be that the rightmost digit is already numeric */
IF dbo.fctisnumericex(SUBSTRING(@string,DATALENGTH(@string),1))=1
BEGIN
SELECT @@posr=DATALENGTH(@string)
END ELSE BEGIN
SELECT @@i=DATALENGTH(@string)
SELECT @@c=SUBSTRING(@string,@@i,1)
WHILE dbo.fctisnumericex(@@c)!=1 BEGIN
SELECT @@i=@@i-1
IF @@i<1 BEGIN BREAK END
SELECT @@c=SUBSTRING(@string,@@i,1)
END
SELECT @@posr=@@i
END
/* so have we got any numeric part inside that string? */
IF @@posr>0 BEGIN
/* yep. see how long it lasts */
SELECT @@i=@@posr
SELECT @@c=SUBSTRING(@string,@@i,1)
WHILE dbo.fctisnumericex(@@c)=1 BEGIN
SELECT @@posl=@@i
SELECT @@i=@@i-1
IF @@i<1 BEGIN BREAK END
SELECT @@c=SUBSTRING(@string,@@i,1)
END
/* separate now the parts of the string */
IF @@posl>1 BEGIN SELECT @@token1=SUBSTRING(@string,1,@@posl-1) END ELSE BEGIN SELECT @@token1='' END
SELECT @@token=SUBSTRING(@string,@@posl,@@posr-@@posl+1)
IF @@posr<DATALENGTH(@string) BEGIN SELECT
@@token3=SUBSTRING(@string,@@posr+1,DATALENGTH(@string)-@@posr) END ELSE BEGIN SELECT @@token3='' END
/* increment the numeric part */
SELECT @@token=convert(varchar(255),convert(int,@@token)+1)
END ELSE BEGIN
/* no numeric part at all. start with 1 at the end */
SELECT @@token1=@string
SELECT @@token='1'
SELECT @@token3=''
END
/* recompose the string and trim to max length if necessary */
RETURN SUBSTRING(@@token1+@@token+@@token3,1,@maxlen)
END

View 4 Replies View Related

Stripping Non-numeric Chars From A Field String - SQL 2000

Jul 20, 2005

Hi there - I would like to share this strip of code with our SQL 2000DBA community. The code below strips all non-numeric characters from agiven string field and rebuilds the string. Very simple, but I had tobuild it from scratch due the lack of info on this specific matter. Iam sure there are better solutions out there, although I will be gladif this script can help anyone. Feel free to modify and comment itback.Regards,Rubem Linn JuniorMCSE, .NET developerWeb Apps Specialist------------------------------------------------------- BEGIN---------------------------------------------------DECLARE @String_Length AS INTEGER -- Length of the given stringDECLARE @Original_String as NVARCHAR(50) -- The field to stripnon-numeric charsDECLARE @counter as integer -- simple counter variableDECLARE @Stripped_String as nvarchar(50) -- The field after beenstripped-- Get the length of the field (string) to be parsedSELECT @String_Length = len(someStringField) FROM SomeTable WHEREFilterID = 001-- Get the field (string) to be parsedSELECT @Original_String = someStringField FROM SomeTable WHEREFilterID = 001-- Set counter variable to 1SELECT @counter = 1-- Reset this variableSELECT @Stripped_String = ''-- Initiate loop from 1 to the Length of the given stringWHILE (@counter) <= @String_LengthBEGIN-- Check if the char in the lap is numericif substring(@Original_String,@counter,1) LIKE '[0-9]'BEGIN-- Load this variable with the non-numeric-- data stripped from the original stringselect @Stripped_String = @Stripped_String +substring(@Original_String,@counter,1)END-- Increment the counter by oneselect @counter = @counter + 1END-- Print the original string with all charactersPRINT @Original_String-- Print the numeric data that was stripped outPRINT RTRIM(LTRIM(@Stripped_String))

View 1 Replies View Related

Data Conversion - Numeric To String Loosing Precision

Oct 26, 2006

Hi All,

i'm using a "data conversion" object to convert a numeric field to a string just before i save the record set to the database.

the problem is when this numeric field is > 0 it looses the precision on its decimal value.

example, if numeric value is 0.32

after converting this to a string, the new value will be : .32

it's lost the 0 infront of it. i can't do this converion in the query level because it's a derived field, so i need to convert it to a string before stroing it.

when converting to string i'm using the code page 1252 (ANSI - Latin I). i also tried with unicode string both looses this 0 infront.

can any one help on this?

View 7 Replies View Related

Power Pivot :: Converting Text String To Numeric Value

Jun 11, 2015

I have a column that I'm trying to call into a calculated measure to determine an expected contract amount (Terms in Month). The problem is that some of the terms are defined as text strings (MTM, Coterminous, One-time) while others are numbers (12, 36, etc). TheĀ entire column is recognized as text.Ā I have a numeric value that management has agreed would be an acceptable substitution (MTM=1, Coterminous=6) and so on. I can't however, figure out how to convert those texts to a number since they are different data types. I've tried a nested IF statement, as well as a LOOKUPVALUE..I'm doing this in Power Pivot, so am limited to DAX formulas

View 4 Replies View Related

Excel Source Numeric/string Data Problem

Oct 26, 2006

i've been reading some problems with excel source data being force as
numeric type when there are string/numeric type in the data, but adding
IMEX=1 to the extended properties will fix this problem...this is true
but not in my case...



say my excel file have about 40 rows..if row 1-39 in column A are all
NULL and row 40 has a string in it, the string in row 40 will not be
converted and the excel source is forcing this column A data type to be
numeric..having IMEX=1 in there does not work..however..if i add a
string anywhere in row 1-8 in column A, the the string in row 40 will
be converted because the external data type now is a string..



anyone know how to solve this problem?

View 3 Replies View Related

How To Remove Non-Numeric Or Non-alphameric Characters From A String In Sql Server 2005

May 16, 2007

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
 
 

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved