Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

Every Database Management System focuses on specific features and applications. I am using mysql, probably as it is popular and satisfies common needs. However, I came across a problem, which needs advanced string functions (probably at the level of a scripting language), posted as a question with a solution in mysql though NOT straightforward (How to split a string to and get info from another table in mysql).

Thus, I thought that there should be other RDBMSes with appropriate functions suitable for such tasks. Can you suggest alternative RDBMSes for splitting a cell (string) and connect the sub-cells to other tables? Even, it is not necessary to build a virtual table by JOIN, just to fetch appropriate values from another table.

share|improve this question
3  
You might want to change the title to something less likely to get your question closed as "not constructive". I think you are just asking which RDBMSs support something like table valued functions. – Martin Smith Dec 12 '12 at 14:28
2  
If your idea of 'advanced' RDBMS functions is string splitting then you're barking up the wrong tree. – Remus Rusanu Dec 12 '12 at 14:33
1  
Look at Nested Tables – Remus Rusanu Dec 12 '12 at 15:00
1  
Why are you splitting a string so frequently that it becomes part of the criteria for what database engine to use? To @RemusRusanu's point, if you are storing a value in a cell that is a composite, you're violating basic principles of normalization. If you are going to work with any relational database, you are going to be much better served normalizing the data model. That's the "relation" in "relational database"-- any relational database is going to perform better and be easier to use when your data model is normalized. – Justin Cave Dec 12 '12 at 18:53
1  
@All - Sure, on rare occasions, it is necessary. But I'm hard-pressed to imagine a situation where it is beneficial from a performance standpoint to denormalize a database by creating a delimited, composite value. Occasionally, it's reasonable to denormalize by, say, storing redundant data or storing a computed value. But I've never come across a situation where it would have been helpful to store composite data rather than creating a proper child table. – Justin Cave Dec 12 '12 at 19:48
show 5 more comments

3 Answers

up vote 2 down vote accepted

I can't speak (much) for other platforms, but here's what such a function would look like in SQL Server. This is the same one we use on our production server in several places. (The pos column is there for cases where order needs to be preserved.) You could also implement this as a .NET CLR user-defined function, essentially acting as a wrapper to the String.Split() method.

CREATE FUNCTION [dbo].[fn_split](
    @string nvarchar(max),
    @delimiter nchar(1)
)
RETURNS @output TABLE (
    string nvarchar(max),
    pos int IDENTITY(1,1)
)
AS
BEGIN
    DECLARE @split int
    DECLARE @start int
    SET @split = CHARINDEX(@delimiter, @string, 0)
    SET @start = 1

    WHILE @split != 0
    BEGIN
        INSERT INTO @output (string)
        VALUES (SUBSTRING(@string, @start, @split-@start))

        SET @start = @split + 1
        SET @split = CHARINDEX(@delimiter, @string, @start)
    END

    INSERT INTO @output (string)
    VALUES (RIGHT(@string, DATALENGTH(@string) / 2 - @start + 1))

    RETURN
END

Using such a function would look something like this:

SELECT
    t1.id,
    t2.SomeColumn
FROM PoorlyNormalizedTable t1
    CROSS APPLY dbo.fn_split(t1.PoorlyNormalizedColumn, ',') s
    INNER JOIN SomeOtherTable t2
        ON s.string = t2.KeyColumn
share|improve this answer
This is a subtle solution for my case, but unfortunately I'm on linux. – All Dec 12 '12 at 17:00
up vote 1 down vote

imho Perl is the king of the string. And in PostgreSQL you can use PL/Perl (trusted and untrusted) for all kinds of stuff, including string handling. We use it a lot and are very happy with it.

Without PL/Perl, you can also use many SQL functions in PostgreSQL for handling strings and arrays. PostgreSQL has pretty good support for regexes as well.

share|improve this answer
Perl/Python/PHP all can handle string functions well, but the problem is that fetching data and updating the database for thousands of queries is not efficient in practice. I've heard that PostgreSQL is fairly advanced, but I did not find more string functions comparing with mysql (of course, by a quick review). – All Dec 12 '12 at 17:02
Check regexp_split_to_array or regexp_split_to_table – Frank Heikens Dec 12 '12 at 17:21
up vote 0 down vote

ParAccel most likely has the most advanced UDF API available.

share|improve this answer
Hi mike, have you used ParAccel yourself? – Jack Douglas♦ Mar 6 at 9:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.