CODEWITHSUNDEEP
sqlsubstring
Chaitanya Chowdary
Chaitanya Chowdary

Reputation: 1

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

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

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

Upvotes: 0

Views: 40

Answers (2)

Sajjad Ahmed
Sajjad Ahmed

Reputation: 367

The simplest method is to use LIKE:

SELECT CASE WHEN 'FDAJLK' LIKE '%[0-9]%' THEN 'True' ELSE 'False' END;  -- False
SELECT CASE WHEN 'FDAJ1K' LIKE '%[0-9]%' THEN 'True' ELSE 'False' END;  -- True

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

May be this will help you. It assumes that you have space after number. And it will pick just first number:

SELECT  CASE WHEN PATINDEX('%[0-9]%', @s) = 0 THEN NULL
             ELSE SUBSTRING(@s, PATINDEX('%[0-9]%', @s),
                            CHARINDEX(' ', @s, PATINDEX('%[0-9]%', @s))
                            - PATINDEX('%[0-9]%', @s))
        END

Upvotes: 0

Related Questions