Solved

SQL Parse or split?

Posted on 2011-09-24
8
350 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 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

695 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