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
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.
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)
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.
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')