Pulling number out a string and inserting

[WarcraftPlayer]

I have Temp table A that looks something like this

Customerid Notes CreateDate
12345 FEDEX #123456789 4/7/2005
12377 Customer complained 4/7/2005

I also have another permanent table B that looks something like this

Customerid Notes OrderTracking CreateDate


I need to take the FEDEX # and take out the number and put it in the ordertracking column so it should like this

Customerid Notes OrderTracking CreateDate
12345 FEDEX #123456789 123456789 4/7/2005


This is the query I currently have. What do i have to do to split the tracking number out of the notes field and put it into the ordertracking on insert.

INSERT INTO table (Customerid, Notes,Createdate)
SELECT CustomerId,Notes,CreateDate FROM Table A



Thanks in advance

 

[amrita418]

Can there be case of notes being 'FEDEX #123A34D'?

If yes what is the expected output then?

Regards,
AA

 

[amrita418]

Also, is there any pattern for the position of number?

For example number starts at 7th character if the notes is for FEDEX.

 

[amrita418]

If for every notes, number occurs at a fixed position then using substring should do it:

INSERT INTO Table1 (Customerid, Notes,OrderTracking, Createdate)
SELECT CustomerId,Notes,substring(Notes, 7, len(Notes)), CreateDate FROM Table1

If the position is arbitrary then use the following function that returns the number part:

CREATE FUNCTION dbo.GetNumber(@notes VARCHAR(30))
RETURNS INT
AS
BEGIN
DECLARE
	@out INT,
	@v_flag INT 
SET 	@out = 0
SET 	@v_flag = 0
	WHILE @v_flag = 0
	BEGIN
		IF(ISNUMERIC(LEFT(@notes, 1)) = 0)
		BEGIN
			SET @notes = SUBSTRING(@notes, 2, LEN(@notes))
		END
		ELSE
		BEGIN
			SET @out = @notes
			SET @v_flag = 1			
		END
	END
RETURN 	@out
END

Then the select stmt should be

INSERT INTO Table1 (Customerid, Notes, OrderTracking, Createdate)
SELECT CustomerId,Notes, dbo.GetNumber(Notes), CreateDate FROM Table1

PS: This solution assume that all characters after an int are int (i.e. no values like 2234A3).

Regards,
AA

 

[mrdenny]

PatIndex and CharIndex and SubString will help you out here. Check out thier usage in BOL.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[WarcraftPlayer]

Thanks you 2 went with mrdenny's suggestion

this is my query
SELECT SUBSTRING ( notes, CHARINDEX('#', notes,2)+3 , 30 From table1
WHERE notes Like '%FEDX Tracking #%' OR notesLike '%FEDEX Tracking #%'

the only thing is some of the results have a |sh at the end 123456789|sh and sometime other notes

who can i get my query to cut off right after the number
i tired this

SELECT SUBSTRING ( notes, CHARINDEX('#', notes,2)+3 , left(notes,CHARINDEX('|',notes) - 1 )) From table1
WHERE notes Like '%FEDX Tracking #%' OR notesLike '%FEDEX Tracking #%'

but it does not work because most of the time it does not have the | character

 

[mrdenny]

I'd probably recommend a case statement in the select.

select case when patindex('|sh', notes) <> 0 then
        SUBSTRING ( notes, CHARINDEX('#', notes,2)+3 , left(notes,CHARINDEX('|',notes) - 1 )) 
    else
        SUBSTRING ( notes, CHARINDEX('#', notes,2)+3 , 30 )
    end
from table1
WHERE notes Like '%FEDX Tracking #%' OR notesLike '%FEDEX Tracking #%'

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)