CODEWITHSUNDEEP
sql-server-2005
user1030181
user1030181

Reputation: 2015

I have string with number, how to get number out of this string in SQL Server

I have string something like ACGI SHIPPING (3471) and I need to get 3471 number out of it.

If there is no number just print 0, in SQL Server

Upvotes: 0

Views: 29

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Here is a solution:

DECLARE @s VARCHAR(100) = 'ACGI SHIPPING (3471)'
SELECT CASE WHEN PATINDEX('%[0-9]%', @s) > 0 
            THEN REPLACE(SUBSTRING(@s, PATINDEX('%[0-9]%', @s), LEN(@s)), ')', '') 
            ELSE 0 END

You are searching for first digit and take substring from that position to end. And then replace ')' with blank.

Upvotes: 1

Related Questions