Split/Explode Comma Delimited String with Sybase SQL Anywhere

Split/explode comma delimited string with Sybase SQL Anywhere

Like Mikael Eriksson said, there is answer at dba.stackexchange.com with two very good solutions, first with use of sa_split_list system procedure, and second slower with CAST statement.

For the Sybase SQL Anywhere 9 sa_split_list system procedure not exist, so I have made sa_split_list system procedure replacement (I used parts of the code from bsivel answer):

CREATE PROCEDURE str_split_list
(in str long varchar, in delim char(10) default ',')
RESULT(
  line_num integer,
  row_value long varchar)
BEGIN
  DECLARE str2 long varchar;
  DECLARE position integer;

   CREATE TABLE #str_split_list (
   line_num integer DEFAULT AUTOINCREMENT,
   row_value long varchar null,
   primary key(line_num));

   SET str = TRIM(str) || delim;
   SET position = CHARINDEX(delim, str);

   separaterows:
   WHILE position > 0 loop
       SET str2 = TRIM(LEFT(str, position - 1));
       INSERT INTO #str_split_list (row_value)
       VALUES (str2);
       SET str = RIGHT(str, LENGTH(str) - position);
       SET position = CHARINDEX(delim, str);
    end loop separaterows;

   select * from #str_split_list order by line_num asc;

END

Execute the same way as sa_split_list with default delimiter ,:

select * from str_split_list('1234,23,56,576,1231,567,122,87876,57553,1216')

or with specified delimiter which can be changed:

select * from str_split_list('1234,23,56,576,1231,567,122,87876,57553,1216', ',')

parsing in sybase a fields with different digits

I guess you may easily pull this using SUBSTRING/CHARINDEX function combination -

SELECT SUBSTRING(Field1, 1, CHARINDEX(';', Field1) -1)
FROM YOUR_TABLE

How do i format a sql numeric type with commas on Sybase SQLAnywhere?

Have you tried giving a varchar (20) for example instead ? something like :

SELECT CONVERT(varchar(20), CAST(987654321 AS money), 1)

In SqlAnywhere money datatype is a domain, implemented as NUMERIC(19,4).

in CAST function , If you do not indicate a length for character string types, the database server chooses an appropriate length. If neither precision nor scale is specified for a DECIMAL conversion, the database server selects appropriate values.

So maybe this is what's causing the issue, what do you get as output ? do you get 987654321.00 , or just 987654321 ?

Update:

My last suggestion would be using insertstr() function and loop through the char value of your number to insert comma every 3 digits .. this is not the cleanest/easiest way but apparently SQLAnywhere deal with money datatype as normal NUMERIC datatype ...

insertstr() documentation is here.

I would give you a code sample but I don't have SQLAnywhere installed to test it ...

How do I split a delimited string so I can access individual items?

You may find the solution in SQL User Defined Function to Parse a Delimited String helpful (from The Code Project).

You can use this simple logic:

Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null

WHILE LEN(@products) > 0
BEGIN
    IF PATINDEX('%|%', @products) > 0
    BEGIN
        SET @individual = SUBSTRING(@products,
                                    0,
                                    PATINDEX('%|%', @products))
        SELECT @individual

        SET @products = SUBSTRING(@products,
                                  LEN(@individual + '|') + 1,
                                  LEN(@products))
    END
    ELSE
    BEGIN
        SET @individual = @products
        SET @products = NULL
        SELECT @individual
    END
END

search entire Sybase database for a string of data

You can try the following:

CREATE TABLE "DBA"."table_track" (
"Table_name" VARCHAR(200) NULL,
"Column_name" VARCHAR(200) NULL,
"ls_search" VARCHAR(2000) NULL
) IN "system";

CREATE OR REPLACE PROCEDURE "DBA"."sp_get_data"(as_search   varchar(2000))
BEGIN
    DECLARE cur_test NO SCROLL cursor for select table_Name, column_name from Table_List ;

    DECLARE ls_sql varchar(20000);
    DECLARE ls_table_name varchar(80);
    DECLARE ls_column_name varchar(80);
    DECLARE ld_count Numeric(10,0);  
    DECLARE le_total_column_count   INTEGER;
    DECLARE le_counter  INTEGER;

    If Exists (SELECT 1 FROM sysobjects WHERE name ='Table_List' AND Type='U') Then
        Drop Table Table_List;
     End IF;

    SELECT
        sys.SYSTABLE.table_name,
        sys.SYSTABCOL.column_name   
    Into Table_List
    FROM
        sys.SYSTABLE
    Inner Join
         sys.SYSTABCOL on sys.SYSTABLE.table_id = sys.SYSTABCOL.table_id
    Where
        sys.SYSTABLE.table_type = 'BASE'
        AND sys.SYSTABLE.creator = 1
        and count > 0
        and width >= 4
        AND sys.systable.table_name not in ('table_track')
        AND (sys.SYSTABCOL.base_type_str like '%char%' OR sys.SYSTABCOL.base_type_str like '%xml%');

    select count() into le_total_column_count from table_list;

    Message as_search to client;

    set le_counter = 0;

    Open cur_test;
    lp: loop
     Fetch Next cur_test into ls_table_name, ls_column_name; 
        If SQLCODE <> 0 Then
            LEAVE lp
        End If;

        SET le_counter = le_counter + 1;

        message 'Search column ' + string(le_counter) + ' of ' + string(le_total_column_count) TO CLIENT;

        Set ls_sql = 'select count(*) into ld_count from "' + ls_table_name + '" where "' + ls_column_name + '" like ''%'+ as_search + '%''';      
        EXECUTE (ls_sql);
        If SQLCODE <> 0 Then
            return -1;
        End If;

        If ld_count <> '' and ld_count is not null  Then
            If ISNULL(ld_count,0) > 0 Then
                Insert into table_track (table_name,column_name,ls_search) values (ls_table_name,ls_column_name,as_search);
            END If;
        END If ;

    End Loop;
    Close cur_test;
    Deallocate cur_test;
    commit;
    // Select * from table_track;
END; 

call sp_get_data('test');

select * from table_track;

I think you can get the idea from this and then you can make changes according to your requirement.

Turning a Comma Separated string into individual rows

You can use the wonderful recursive functions from SQL Server:

Sample table:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
);

INSERT Testdata SELECT 1,  9, '18,20,22';
INSERT Testdata SELECT 2,  8, '17,19';
INSERT Testdata SELECT 3,  7, '13,19,20';
INSERT Testdata SELECT 4,  6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';

The query

WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)
SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

Output

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4

Comma-separated String into Table's Column in SQL Server

Create a function:

CREATE FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
    DECLARE @Index INT
    DECLARE @Slice nvarchar(4000)
    -- HAVE TO SET TO 1 SO IT DOESN’T EQUAL ZERO FIRST TIME IN LOOP
    SELECT @Index = 1
    WHILE @Index !=0
        BEGIN
            SELECT @Index = CHARINDEX(@Delimiter,@String) --Getting the indexof the first Occurrence of the delimiter

            -- Saving everything to the left of the delimiter to the variable SLICE
            IF @Index !=0
                SELECT @Slice = LEFT(@String,@Index - 1)
            ELSE
                SELECT @Slice = @String

            -- Inserting the value of Slice into the Results SET
            INSERT INTO @Results(Items) VALUES(@Slice)

            --Remove the Slice value from Main String
            SELECT @String = RIGHT(@String,LEN(@String) - @Index)

            -- Break if Main String is empty
            IF LEN(@String) = 0 BREAK
        END
    RETURN
END

Pass the string @str and the delimiter (,) to the function.

SELECT Items FROM [dbo].[Split] (@str, ',')

It will return the result as a table:

Items

0.00
0.00
1576.95
0.00
4105.88
1017.87
0.00
6700.70

See SQL Fiddle

Split/Explode Comma Delimited String with Sybase SQL Anywhere