Query to return substring from string in SQL Server

Question

I have a user defined function called Sync_CheckData under Scalar-valued functions in Microsoft SQL Server. What it actually does is to check the quantity of issued product and balance quantity are the same. If something is wrong, returns an ErrorStr nvarchar(255).

Output Example:

Balance Stock Error for Product ID : 4

From the above string, I want to get 4 so that later on I can SELECT the rows which is giving errors by using WHERE clause (WHERE Product_ID = 4).

Which SQL function can I use to get the substring?

Is it always the last digit before the semi colon?

sgeddes
– sgeddes

2013-06-28 02:59:36 +00:00
Commented Jun 28, 2013 at 2:59 — sgeddes
– sgeddes, Commented Jun 28, 2013 at 2:59

tsohtan · Accepted Answer · 2013-06-28 03:02:45Z

3

Try this

DECLARE @STR AS VARCHAR(1000)
SELECT @STR='Balance Stock Error for Product ID : 4'

SELECT substring(@STR,charINDEX(':',@STR)+1,LEN(@STR)-charINDEX(':',@STR)+1)

answered Jun 28, 2013 at 3:02

tsohtan

8502 gold badges13 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sgeddes Over a year ago

+1 -- no need for the added charindex at the end -- it will truncate accordingly. Here's a Fiddle Demo: sqlfiddle.com/#!3/d41d8/16396

Aung Kaung Hein Over a year ago

Thank you so much, tsohtan! I got it.

sgeddes · Accepted Answer · 2013-06-28 03:07:12Z

1

I'd use RIGHT for this:

declare @response varchar(100)
set @response = 'Balance Stock Error for Product ID : 4'

select right(@response, len(@response) - charindex(':', @response))

Sample fiddle: http://sqlfiddle.com/#!3/d41d8/16397 (altered from above)

answered Jun 28, 2013 at 3:07

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Comments

bvr · Accepted Answer · 2013-06-28 04:16:27Z

1

Try this

DECLARE @String VARCHAR(100)

SET @String = 'Balance Stock Error for Product ID : 4'

SELECT RIGHT(@string, CHARINDEX(':', REVERSE(@string))-2)

OR

SELECT RIGHT(@string,(LEN(@string)+1)-PATINDEX('%[0-9]%', @string))

answered Jun 28, 2013 at 4:16

bvr

4,8361 gold badge23 silver badges24 bronze badges

Collectives™ on Stack Overflow

Query to return substring from string in SQL Server

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related