Solved

SQL Parse or split?

Posted on 2011-09-24
8
343 Views
Last Modified: 2012-05-12
I have a table with the following:

one
two
one
one
two
three

So a "distinct" query will return:

one
two
three

What if the field contained:

one
two
one
one~four
two
three

Is there a query function that would return:

one
two
three
four

This is a question database and I want to keep multiple "categories" in this fashion using a tilde.
0
Comment
Question by:classnet
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36593984
Something like this:

SELECT DISTINCT LEFT(SomeColumn, CHARINDEX('~', somecolumn + '~') - 1) AS Whatever
FROM SomeTable

Open in new window


By adding on the ~ in the CHARINDEX I make it work whether the original has a tilde or not.
0
 
LVL 3

Accepted Solution

by:
nrbreen earned 500 total points
ID: 36594150
Unfortunately, the simple charindex approach is only extracting the first value on each line, the second and further values are being discarded.

In order to use "distinct" the tilde-delimited values must first be brought out into separate rows.
The UDF  tilde_extras() shown below  does that, on the fly returning a temp table containing all the values.
It caters for any number of values, e.g. one~three~six~ten

-- First substitute your values for MYTABLE and MYCOLUMN  below.
-- Then AFTER running the Create Functions below, run the select distinct by itself

select distinct(value) from dbo.tilde_extras()

--  =========================================

CREATE FUNCTION dbo.tilde_extras()
RETURNS @result table (value varchar(100)) 
as
begin
   declare @varname1 char(100)

    declare cursor1	cursor FAST_FORWARD FOR  select MYCOLUMN  from MYTABLE
	 
	 open cursor1
	 fetch next	from cursor1 into	@varname1
	 WHILE @@FETCH_STATUS = 0
	 begin
        insert @result select value from dbo.tildeSplit(@varname1)
	    fetch next	from cursor1 into	@varname1
	 end   
     close cursor1
     deallocate cursor1
   return
end


CREATE FUNCTION dbo.tildeSplit(@data nvarchar(100))  
RETURNS @result table (value nvarchar(100)) 
AS  
BEGIN 
    DECLARE @pos   INT
    DECLARE @start INT
    DECLARE @len   INT
    DECLARE @end   INT
    DECLARE @delimiter char(1)

    set @delimiter ='~'
    SET @len   = 1
    SET @end   = LEN(@data) + 1
    SET @start = 1
    SET @pos   = 0

    WHILE (@pos < @end)
    BEGIN
        SET @pos = CHARINDEX(@delimiter, @data, @start)
        IF (@pos = 0) SET @pos = @end

        INSERT @result (value) SELECT SUBSTRING(@data, @start, @pos - @start)
        SET @start = @pos + @len
    END

    RETURN
END

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36594284
Hm, I see now that I totally misread the question.  Sorry :)
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 52

Expert Comment

by:_agx_
ID: 36594291
(not for points...)

>> I want to keep multiple "categories" in this fashion using a tilde.

If you've got control over the design, I wouldn't recommend storing multiple values that way.  As you can already see it's difficult to work with.  A normalized table (one value per row) is the way to go imo.

Just my $0.02
0
 

Author Comment

by:classnet
ID: 36595039
_agx_:  I currently use only one table... it has category, question and ID fields.  The category field is long enough to contain tilde separated categories should a question fall into multiple categories.

Are you suggesting a table for categories and then another for questions?  How, in that case, would you assign multiple categories to a question?

Redesign IS very much as option.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36595289
>>Are you suggesting a table for categories and then another for questions?  How, in that case, would you assign multiple categories to a question?<<
Most definitely.  
You need three tables: Questions, Categories and QuestionCategoryXRef.
Categories is just a simple table of all your categories
You will remove the category from Questions and add it to QuestionCategoryXRef.

Then your query becomes quite simple:
SELECT  Category, COUNT(*) NumberOfCategoriesPerQuestion
FROM     QuestionCategoryXRef
GROUP BY
              Category


As you can see it is very simple to not only get your list, but also a count of questions per category as opposed to adding tildes + 1
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36596791
    >> Redesign IS very much as option

@classnet - Great.  Then I would recommend using the structure @acperkins proposed.  You'll find it much easier to work with than delimited lists.
0
 

Author Comment

by:classnet
ID: 36598012
Thanks for the tip _agx_:
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question