?
Solved

SQL Parse or split?

Posted on 2011-09-24
8
Medium Priority
?
351 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 93

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 2000 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 93

Expert Comment

by:Patrick Matthews
ID: 36594284
Hm, I see now that I totally misread the question.  Sorry :)
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 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