find first number

How do I find the first number in a string?

I need to update a field by removing anything that could not be a number. 
Unfortunately, there's no specific number of characters which I can use to 
start and stop. Intentions are to replace everything before the first number 
and after the last number. Fortunately, the numbers I want are grouped 
together, but in the missle of strings.

thanks 


0
shank
2/18/2010 3:03:52 AM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
544 Views

Similar Articles

[PageSpeed] 48

Here is one example:

CREATE TABLE Foo (
  foo_key INT PRIMARY KEY,
  foo_data VARCHAR(35));

INSERT INTO Foo VALUES (1, '1');
INSERT INTO Foo VALUES (2, 'aa21a');
INSERT INTO Foo VALUES (3, 'ab');
INSERT INTO Foo VALUES (4, NULL);
INSERT INTO Foo VALUES (5, 'a5');
INSERT INTO Foo VALUES (6, '');
INSERT INTO Foo VALUES (7, 'af1ad34e');

WITH Clean (foo_key, foo_data, foo_clean)
AS
(SELECT foo_key, foo_data,
         CAST((SELECT SUBSTRING(foo_data, n, 1)
               FROM (SELECT number
                     FROM master..spt_values
                     WHERE type = 'P'
                       AND number BETWEEN 1 AND 100) AS Nums(n)
               WHERE n <= LEN(foo_data)
                 AND SUBSTRING(foo_data, n, 1) LIKE '[0-9]'
               FOR XML PATH('')) AS INT)
  FROM Foo)
SELECT foo_key, foo_data, foo_clean
FROM Clean;

/*

Results:

foo_key     foo_data   foo_clean
----------- ---------- -----------
1           1          1
2           aa21a      21
3           ab         NULL
4           NULL       NULL
5           a5         5
6                      NULL
7           af1ad34e   134

*/

DROP TABLE Foo;

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
2/18/2010 3:17:56 AM
Does this help?

DECLARE @Var NVARCHAR(12);

SET @Var = N'abc123def456'

SELECT PATINDEX('%[^0-9]%', @Var);

SELECT PATINDEX('%[0-9]%', @Var);

SELECT SUBSTRING(@Var, PATINDEX('%[0-9]%', @Var), LEN(@Var));

String functions are defined here:

http://msdn.microsoft.com/en-us/library/ms181984.aspx

"shank" <shank@tampabay.rr.com> wrote in message 
news:%23eaUldEsKHA.6004@TK2MSFTNGP04.phx.gbl...
> How do I find the first number in a string?
>
> I need to update a field by removing anything that could not be a number. 
> Unfortunately, there's no specific number of characters which I can use to 
> start and stop. Intentions are to replace everything before the first 
> number and after the last number. Fortunately, the numbers I want are 
> grouped together, but in the missle of strings.
>
> thanks
> 


0
Jay
2/18/2010 3:26:10 AM
Reply:

Similar Artilces:

Find Where Specific Fonts Have Been Used in a PowerPoint Presentat
Is there a way to find where specific fonts have been used in a PowerPoint presentation. The Contents tab of the Advanced Properties pane list the fonts that are used in a presentation but not where. MS Word will search for fonts with the find dialogue. I am looking for similar functionality with PowerPoint. In article <6CDAC5DB-AFCF-4485-9EAB-5146B5B5411C@microsoft.com>, John B wrote: > Is there a way to find where specific fonts have been used in a PowerPoint > presentation. The Contents tab of the Advanced Properties pane list the > fonts that are used in a...

finding a cell from numerical coordinates #2
is it possible to locate a cell from coordinates such as 50, 75 or 100,98? I'd really like to be able to do this. No need to post the question twice so soon. In 2007 press Alt+1, Formulas tab and check R1C1 reference style -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Chip" wrote: > is it possible to locate a cell from coordinates such as 50, 75 or 100,98? > I'd really like to be able to do this. Hi, In 2007 press Alt+1, Formulas tab and check R1C1 reference style -- If this helps, please click the Yes button. Cheers, Shane Dev...

PO numbers in FRx?
Hello: Can FRx transaction detail reports in drill-down viewer for income statements display PO numbers from GP Purchase Order Processing, or does FRx not get that detailed? childofthe1980s FRx can bring in the originating master number of a transaction, however, I cannot think of a case where the PO number will be the originating master number, since the PO itself is not an accounting transaction. So I think you'll be able to see the receipt and/or vendor invoice numbers there, but not the PO number. -- Victoria Yudin Dynamics GP MVP Flexible Solutions - home of GP Reports htt...

Random Number in Query
Hi. I am trying to get a Random number between 0 and 5 to appear on each row of a query. I can get the same number on each row (it changes every time I run the query) but cannot get a different random number for each row. Basically, I want to create some Test data by adding the Random number to a Received Date and then saving this new date as a Completed Date. I have created a Module to generate a Random number: Function Random_Number() As Integer Randomize Random_Number = Int(Rnd * 6) End Function Then, I call the function from a query: Field = Number: Random_Number() Ca...

Why my default number format changed
I am using Excel 2007. From time to time, I found the default number format changed when I open an exiting excel file. For example, originally its default format was "General", but when I open the file, found it changed to "Custom". How is it happened? How can I prevent? How can I fix it. Thanks in advance! ...

Text to numbers
For years and years, I have been happily using Excel 97 to copy data from a website to my Excel spreadsheet by saving it as an Excel Workbook. I then manipulate the data (multiply, divide etc) without a problem. I have just installed Office 2000 Small Business, and when I try the same copy procedure, all the calculations return #VALUE. It appears that the "imported" data is text (?), with spaces. Not sure though. I realise I haven't explained this too well, but I'm hoping someone can interpret what I am trying to say, and assist. Try selecting the columns with th...

outline won't number after hitting return
I've set up the document per Shauna Kelly's instructions, but can't figure out why it won't continue to number the outline. It'll give me Heading 1, but when I hit return it goes back to "normal". What in the world am I doing wrong? You would need to modify the Heading 1 style so that it is followed by Heading 1 style (though that is not what would normally be wanted as a heading is normally followed by some text. -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a pai...

"Find and Replace" in Sheet names
Is there a way (with code perhaps) to search and replace sheet names? For example, I have 20 sheet names with "Direct (2)" in the name, and I'ld like to replace teh "Direct (2)" in the sheet name to "Net" in each instance. Thanks, Jim One way: Public Sub Direct2ToNet() Const sRepl As String = "Direct (2)" Dim ws As Worksheet Dim nPos On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws If .Name Like "*" & sRepl & "*" Then _ ...

IF ISNUMBER FIND in query design expression
I use the following formula in Excel and it works...I now want to do this in a query. Can I add it as an expression in the design grid in a seperate column and how does the syntax change? I will also want to use TRUE/FALSE for the result. =IF(ISNUMBER(FIND("TRANSF",H2)),"*", "") Try this -- My_Output: IIF(InStr([YourFieldName],"TRANSF")>0,"*", "") -- Build a little, test a little. "gator" wrote: > I use the following formula in Excel and it works...I now want to do this in > a query. C...

number grab
Hi I've been a bit stupid. I have about 6 columns of price data where column R is a net selling price. I've entered these column R numbers as fixed numbers. This is my mistake because I've added a formula to the P column which knocks a percentage off the R column number to give me my purchase price. What I should have done was to have the fixed numbers in the purchase price column P for the selling price column R to derive values using a formula. The other way round in other words. I really don't want to have to work through 650 lines making a note of all the formula dete...

How do i find the elegant resume template
I am using the microsoft publisher and i am looking for the elegant resume template. It is a template that already has a resume typed up and u insert your own info, is anyone familiar with what i am talking about? thanks lisa Here is something for Publisher 2000 http://www.microsoft.com/downloads/details.aspx?FamilyID=2d0215a2-0852-4673-87cb-6e7cfb2de817&DisplayLang=en -- Don ------ Vancouver, USA "lisa" <lisa@discussions.microsoft.com> wrote in message news:D165DBCF-637C-4DA4-ABA4-2AF673AD7096@microsoft.com... >I am using the microsoft publisher and i am look...

Problem with Money finding files after hard drive crash
My hard drive was corrupted and crashed. I was able to preserve data files, including the Money account file and Money backup file. The hard drive was replaced, and I copied the Money data files back onto the hard drive, as well as the Money program. When I try to set up Money again, it cannot find or connect with the data files, despite my specifying the drive location of the files. Is there a way to get that to happen? I don't know if this is part of the problem, but the description of my data files calls them Money 2004 files. The recovery version of Money is Money 2005, wh...

Can't find CD/DVDdrive
My DVD drives have disappeared. they don't show as hardware in control panel. Working fine yesterday - no software changes made. Both have power to them. I've unplugged everything and put a new lead from them to the motherboard. Doesn't appear to be anything physical. Followed various posts on this site. Mr Fixit recognises the problem but can't fix it. Went into registry. Higher filters there but no lower filters. Any suggestions? On 06/20/2010 05:13 AM, Philx5 wrote: > My DVD drives have disappeared. they don't show as hardware in control panel. ...

Running Access Query Returns Incorrect Number of Records
I am running some code which loops through and runs the queries in an Access Database. The count of the records is then stored in a Worksheet. For most of the queries, the figure stored in the Worksheet matches the number of records if you run the query in Access. But for certain queries, the count of the records does not match the number of records when the query is run in Access. If I change the query to a make table query and then base another query on that table, the results are correct. Can anybody suggest running the query from Excel would results in a different number of records bein...

TEXT STATUS -VS- Numbers
We have name of projects with 8 different status. We want to create a chart with the name of the project on the X axis and text status on the Y axis. Can this be done and if so how. Please help DDBeards If you assign numbers 1-8 for each status, you can then hide the 1-8 axis labels, and add custom ones: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ DDBeards wrote: > We have name of projects with 8 different status. We want to cre...

how do I insert sequential numbering on a sheet of 10 pages?
-- Morrell http://www.publishermvps.com/Default.aspx?tabid=95 -- Ed Bennett - MVP Microsoft Publisher ...

SP2 screws Excel. Now only puts in decimal numbers
I installed SP2 for office 2003 yesterday and now anything I type into my excel workbook shows up with .45 instead of 45 which I typed in.. Any ideas? Tools -> Options -> Edit tab. Uncheck "Fixed Decimal Places" HTH knut "Tboz" <Tboz@discussions.microsoft.com> skrev i melding news:42223421-8753-4E47-B4BE-ACED5FD1EF24@microsoft.com... >I installed SP2 for office 2003 yesterday and now anything I type into my > excel workbook shows up with .45 instead of 45 which I typed in.. Any > ideas? tools>options>calculation>precision as displ...

Where can I find the newsgroups that have disappeared from the MS news server?
I've been an enthusiastic user of these wonderful MS newsgroups for years (mostly the Access groups), but I've noticed that Microsoft has been removing them from their news server. Are the discussions continuing on a Web-based forum somewhere? If so, I'm disappointed because I've found reading the message threads with a newsreader like Outlook Express is much more convenient than trying to manage the browser-based forums. But if that's the only thing available, I'd gladly use them. Can anyone tell me where I can go to find the question and answer gro...

Finding sheets
I'm just an inquisitive person, I guess ;-) I would like to create the name of a particular sheet dynamically, then read data from it. For example, ="'"&MONTH(A1)&"."&DAY(A1)&"."&TEXT(MOD(YEAR(A1),100),"00")&"-"&MONTH(B1) &"."&DAY(B1)&"."&TEXT(MOD(YEAR(B1),100),"00")&"'!"&"A3:A50" That will give me the name of a particular sheet. A start date and end date are in A36 and B36, respectively. I would like to take the name of the s...

change font size of first character
I have a template that allows users to print business cards. It displays a form where they enter their name and three other lines of text. I would like to increase the font size of the first character in each word if the user enters the text in upper case. The code I'm using to capture the text from the form is: With ActiveDocument For intLoop1 = 1 To 10 .Bookmarks("Name" & Right("0" & intLoop1, 2)).Range.InsertBefore txtName .Bookmarks("Title" & Right("0" & intLoop1, 2) & "a").Range.InsertBef...

"Find an event" feature in the Windows Live Mail/Calendar
In the Windows Live mail it is quite simple to "Find a message" as we type something in the search box and enter. But, I do not see any search box with the Calendar. Are there any ways to "Find" something in the Windows Live Calendar? Looking forward to hear from someone. Thank you. ...

random sequence of numbers
How do I generate a random sequence of numbers? e.g. simulating pulling the numbers 1 through 25 out of a hat. One way: Put the numbers 1 through 25 in A1:A25. Put this in B1 and copy down to B25: =RAND() Select any cell in the A1:B25 and choose Data/Sort. Sort on column B (ascending or descending, doesn't matter) with no headers. If you need to save the sequence copy A1:A25 to a different location. Since RAND() will recalculate after the sort, you can get another random sequence by choosing Data/Sort again. In article <1eb301c38762$7f1fef90$a301280a@phx.gbl>, "...

Like numbers in a field
I am working with a 25,000 row parts issue listing that was created by parts issue date. There are many repeat numbers in the part number column as the same part would have been issued on several different dates. I would like to be able to combine the like part numbers to know how many parts were issued over the course of all of the dates. Any ideas??? The Column headers are Part Number, Quantity, Unit Price, Total Price and Transaction Date. Create a new query based on your parts issue listiong table. Drag the Part number field into the query grid twice. Click on the Total...

Cashbook Deposit Number Error
We are having a problem with deposit numbers in one of our cashbooks in that every ten numbers, when it gets to a number ending in zero, it drops the zero off the end and we have to reset it. As an example we might use deposit number 4458, then 4459, and the next deposit number will come up not as 4460 but as 446. We can then reset it to 4460 and this works, then we will get 4461, 4462 etc, until we save 4469 and then the next deposit comes up as 447 instead of 4470. As far as I am aware we have not changed any settings so I'm in the dark as to why this is happening. ...

Sorting data that has both text & numbers.
I have a spreadsheet that has a column that contains both text and numbers. ex 1, 100, 101, 100A, 111B, 1251 etc. How do I get this to sort where the alpha letter that follows the # is in # sequence? 100, 100A, 101, 101A etc. The #'s containing the alpha letters sort at the end. I have tried formatting the cell, general, number and text and I'm still having problems. Hi, You will have to insert a column and separate the text from the number then sort. - mark >-----Original Message----- >I have a spreadsheet that has a column that contains both text and numbers. >...