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

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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)  
Jaded Developer
Join Date: Nov 2004
Location: out on a limb
Posts: 12,296
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
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
Reply With Quote
  #3 (permalink)  
Resident Curmudgeon
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,815
An NZDF solution would be:
DROP FUNCTION dbo.KludgeNormalization
CREATE FUNCTION dbo.KludgeNormalization(
   @garbage      NVARCHAR(50)
   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)

   ItemId		INT
,  ReturnDesc   NVARCHAR(50)

   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]%'

In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #4 (permalink)  
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

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