Solved

SQL Parse or split?

Posted on 2011-09-24
8
345 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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…

685 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