If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Return only part of the data in a column

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 2
Return only part of the data in a column

I have a table with 2 columns ItemID and ReturnDesc. A sample of the data is:
ItemId ReturnDesc
1244 Did not want
1244 Wrong color
3426 Return came with ticket 426
3571 Ticket 584 was not included



The only information I want to have returned is the number following the text 'ticket'. How do I do this?
Reply With Quote
  #2 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,090
Proper design of the table is te smart aleck response.
Failing that you may be able to extract what you want using string functions in SOL.
You can aksodo this in whatever is consuming the data, IE the front end
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #3 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,742
An NZDF solution would be:
Code:
DROP FUNCTION dbo.KludgeNormalization
GO
CREATE FUNCTION dbo.KludgeNormalization(
   @garbage      NVARCHAR(50)
) RETURNS INT
AS BEGIN
   DECLARE @i INT, @j INT, @k INT
   SET @i = 7 + PatIndex(N'%Ticket [0-9]%', @garbage)
   SET @j = PatIndex(N'%[^0-9]%', SubString(@garbage, @i, 50))
   IF @i < 8 OR 0 = @j SET @k = 0 ELSE SET @k = Cast(SubString(@garbage, @i, @j) AS INT)
   RETURN Cast(@k AS INT)
END
GO

CREATE TABLE #a (
   ItemId		INT
,  ReturnDesc   NVARCHAR(50)
   )

INSERT INTO #a (
   ItemId, ReturnDesc ) VALUES
   (1244, 'Did not want')
,  (1244, 'Wrong color')
,  (3426, 'Return came with ticket 426 ')
,  (3571, 'Ticket 584 was not included')

SELECT *, dbo.KludgeNormalization(ReturnDesc)
   FROM #a
   WHERE  ReturnDesc LIKE '%Ticket%[0-9]%'

DROP TABLE #a
-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 2
Thanks for the help and yes it would have been great if the table would have been designed differently!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On