Solved

SQL Parse or split?

Posted on 2011-09-24
8
342 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
 
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

914 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now