How to Get a Number which is present in a line of Text in a column of SQL Table?

Question

0.00/5 (No votes)

See more:

I am having a line of text like this:-

"Chaitanya was having 10 Rupees."

I need the number 10 to be displayed as result.

The line will not have same characters every time.

After 5 minutes the line will be changed as:-

"Chaitanya will be having 10 Rupees."

So here the length of the sentence is going to change. But i need to get the result as 10. I need Help with this.

I have tried some code but its not working. The code i tried is:-

SQL

SELECT TOP 1 FROM ALARMS WHERE S.SUBSTRING(Text,26,2) ORDER BY [EventTime] DESC

Posted 2-Apr-15 19:57pm

Krishna Chaitanya Bezawada

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-04-02T20:10:00

Solution 1

Try this:

SQL

DECLARE @text VARCHAR(255) = 'Chaitanya was having 10 Rupees.'

SELECT SUBSTRING(Text, startpos, endpos-startpos) AS MyNumber
FROM
(
    SELECT Text, PATINDEX('%[0-9]%', Text) AS startpos, CHARINDEX(' ', Text, PATINDEX('%[0-9]%', Text)) AS endpos
    FROM Alarms
    WHERE PATINDEX('%[0-9]%', Text)>0
) AS T

Posted 2-Apr-15 20:10pm

Maciej Los

Updated 2-Apr-15 22:06pm

v3

Comments

Krishna Chaitanya Bezawada 3-Apr-15 2:19am

It is Working as per Your Code. But My doubt is "You are Writing entire sentence in the Query and declaring as @text". But my sentence is already present in the table, So i have to pick it up from the table, We should not Write or Declare it Manually. For Example, "The Sentence is Present in a column name 'Text' in Table 'Alarms'". So here i have to take it from the table and display the number present in it.

Maciej Los 3-Apr-15 2:25am

Just replace @text with then name of field you want to use ;)

Krishna Chaitanya Bezawada 3-Apr-15 2:26am

I have already tried it. But there is no use. There is no Updated Result.

Maciej Los 3-Apr-15 2:48am

?See updated answer...

Krishna Chaitanya Bezawada 3-Apr-15 3:01am

When Executing It I am receiving the following Error:-

"An expression of non-boolean type specified in a context where a condition is expected, near ')'."

Maciej Los 3-Apr-15 4:07am

Please, remove last closing parenthesis.

Sascha Lefèvre 3-Apr-15 3:06am

The where-clause has one closing brace too many ;)
Another SQL-snippet for my collection, +5!

Maciej Los 3-Apr-15 4:06am

Thank you, Sascha ;)

OriginalGriff · Answer 2 · 2015-04-02T20:12:00

To be honest, SQL is a poor choice of language to do this: it isn't a text processor, and it's string handling is basic at best.
You can do it:this[^] has an example at the bottom which extracts all the digit characters from a string, and you could modify it to do what you want, probably fairly easily.
But... It'll always be a bit dodgy for exactly what you want.

You would be much better off f doing this in a "normal" programming language, preferably one with Regex handling. It's very simple to build a reliable solution in VB or C# for example.

Solai Raja · Answer 3 · 2015-04-02T20:14:00

Solution 3

you should use the Regular expression, but SQL dosen't have RegEx.
But you can refer the below link for creating custom RegEx functionality for SQL Server

Regular Expressions in MS SQL Server 2005/2008[^]

Posted 2-Apr-15 20:14pm

Solai Raja