Results 1 to 4 of 4
  1. #1
    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?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,448
    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 on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    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.

  4. #4
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •