Extract Parts of String

Transact-SQL
1

I have an nvarchar fields that i would like to get the first 3 numeric string as my carrier code. How can i extract just the first 3 numeric parts. I made a query but could not get the exact result. thank you in advance.

see below my DDL and sample result.

select  Item,
		Case when LEFT(item,3)='CAR' then substring(item, 4, PATINDEX('%[1234567890]%', item) -1) 
            when LEFT(item,4)='SALB' then substring(item, 5, PATINDEX('%[1234567890]%', item) -1) 
       end as Carrier
from #sample 


Item ---------------Carrier
COR4000470iPH616GRY----400
COR4000450iPH5C16PNK---400
COR4001711DRDULTRARED--400
COR4004430M8SIL--------400
SALV2350425iPH5C16WHT--235
SALV2390470iPH6128GRY--239
2

I modified my query and this is the codes which is getting the exact number.
Is there any other method to get the exact numer. i try this code running 150,000 records and it gives me a little bit time to process.

Select left(right(Item, len(Item) - (PatIndex('%[0-9]%', Item )-1) ),3)
from #sample.

3

you can use this function
CREATE FUNCTION [dbo].[udf_GetNumeric]
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
while executing u can use this querry:
declare @sample varchar(20)='qwerty1234'
select substring(dbo.udf_GetNumeric,1,3)

4

I wouldn't do that unless you happen to like bad performance

5 
select  Item,
	SUBSTRING(Item, PATINDEX('%[0123456789][0123456789][0123456789]%', item), 3) AS Carrier
from ...
6

Thank you very much guys..

7

Hi Guys
What if i will pull only those char string after the numeric like CAR4000470iPH616GRY, the result should be iPH6. Just want to know if this can be done by case statement or any other alternative solution. thank you.

item-------------------carrier--model code
==============================================
CAR4000470iPH616GRY----400--iPH6
CAR4000450iPH5C16PNK---400--iPH5C
CAR4001711DRDULTRARED--400--DRDULTRA
CAR4004430M8SIL--------400--M8
SALV2350425iPH5C16WHT--235--iPH5C
SALV2390470iPH6128GRY--239--iPH6
FRU300VS980------------239--VS980
FRUB300DRDMAX16BLK-----239--DRDMAX
TP3001720DRDMINIBLK----239--DRDMINI
8

Hi have a new requirements to get the character string from the left before the number. May I ask your help guys on how to pull this char. thank you.

below is a sample result and DDL

[code]create table #1
(item nvarchar(35))
insert into #1(item)values('CAR4000470iPH616GRY')
insert into #1(item)values('CAR4000450iPH5C16PNK')
insert into #1(item)values('CAR4001711DRDULTRARED')
insert into #1(item)values('CAR4004430M8SIL')
insert into #1(item)values('SALV2350425iPH5C16WHT')
insert into #1(item)values('SALV2390470iPH6128GRY')
insert into #1(item)values('FRU300VS980')
insert into #1(item)values('FRUB300DRDMAX16BLK')
insert into #1(item)values('TP3001720DRDMINIBLK')

item-------------------category

CAR4000470iPH616GRY----CAR
CAR4000450iPH5C16PNK---CAR
CAR4001711DRDULTRARED--CAR
CAR4004430M8SIL--------CAR
SALV2350425iPH5C16WHT--SALV
SALV2390470iPH6128GRY--SALV
FRU300VS980------------FRU
FRUB300DRDMAX16BLK-----FRUB
TP3001720DRDMINIBLK----TP[/code]

9

you should be able to use the technique in the solution provided earlier to get what you want.

10

This is the initial query but did not get some character. if I change it to 4 it gets also the numeric.

select SUBSTRING(item ,PATINDEX('%[^0-9]%',item ),3) 'NonNumeric Character'
from #s1

11
  1. use PATINDEX to find the position of the numeric.
    2 use LEFT JOIN to get the non-numeric