Split Given String and Prepare Case Statement

Split given string and prepare case statement

Clean setup:

CREATE TABLE tbl (
  given_date date
, set_name varchar
);

Use a singular term as column name for a single value.

The data type is obviously date and not a timestamp.

To transform your text parameters into a useful table:

SELECT unnest(string_to_array('2001-01-01to2001-01-05,2001-01-10to2001-01-15', ',')) AS date_range
     , unnest(string_to_array('s1,s2', ',')) AS set_name;

"Parallel unnest" is handy but has its caveats. Postgres 9.4 adds a clean solution, Postgres 10 eventually sanitized the behavior of this. See below.

Dynamic execution

Prepared statement

Prepared statements are only visible to the creating session and die with it. Per documentation:

Prepared statements only last for the duration of the current database session.

PREPARE once per session:

PREPARE upd_tbl AS
UPDATE tbl t
SET    set_name = s.set_name
FROM  (
   SELECT unnest(string_to_array($1, ',')) AS date_range
        , unnest(string_to_array($2, ',')) AS set_name
   ) s
WHERE t.given_date BETWEEN split_part(date_range, 'to', 1)::date
                       AND split_part(date_range, 'to', 2)::date;

Or use tools provided by your client to prepare the statement.

Execute n times with arbitrary parameters:

EXECUTE upd_tbl('2001-01-01to2001-01-05,2001-01-10to2001-01-15', 's1,s4');

Server-side function

Functions are persisted and visible to all sessions.

CREATE FUNCTION once:

CREATE OR REPLACE FUNCTION f_upd_tbl(_date_ranges text, _names text)
  RETURNS void AS
$func$
UPDATE tbl t
SET    set_name = s.set_name
FROM  (
   SELECT unnest(string_to_array($1, ',')) AS date_range
        , unnest(string_to_array($2, ',')) AS set_name
   ) s
WHERE  t.given_date BETWEEN split_part(date_range, 'to', 1)::date
                        AND split_part(date_range, 'to', 2)::date
$func$  LANGUAGE sql;

Call n times:

SELECT f_upd_tbl('2001-01-01to2001-01-05,2001-01-20to2001-01-25', 's2,s5');

SQL Fiddle

Superior design

Use array parameters (can still be provided as string literals), a daterange type (both pg 9.3) and the new parallel unnest() (pg 9.4).

CREATE OR REPLACE FUNCTION f_upd_tbl(_dr daterange[], _n text[])
  RETURNS void AS
$func$
UPDATE tbl t
SET    set_name = s.set_name
FROM   unnest($1, $2) s(date_range, set_name)
WHERE  t.given_date <@ s.date_range
$func$  LANGUAGE sql;

<@ being the "element is contained by" operator.

Call:

SELECT f_upd_tbl('{"[2001-01-01,2001-01-05]"
                  ,"[2001-01-20,2001-01-25]"}', '{s2,s5}');

Details:

• Unnest multiple arrays in parallel

String split or other similar SQL Server functions with case statement

Good day,

The requirement is not fully clear since in the description you say "The goal is to have just names and not XX." but in the requested result you don't have the text "Article 3" which exists in the source.

If you simply want to remove the ", XX" then we can use simple REPLACE like bellow:

WITH WriterTbl AS (
    SELECT 'Sabao Fulano, XX, Sapato Feio, XX, Jose Perreira, XX' AS Writer UNION ALL
    SELECT 'Toze Jose, XX' UNION ALL
    SELECT 'Feijao Mauricio, XX Article 3'
)
select REPLACE(WriterTbl.Writer,', XX',', ')
from WriterTbl

But if you actually want to remove the parts that start with ", XX" and you requirement is to use STRING_SPLIT as you asked in the original question (assuming you will not change the question), then we can use STRING_SPLIT to find the parts of the text we need to remove and than we can use the function STRING_AGG in order to concatenate the text again

There is one important point which you must remember and I am pretty sure that most people do not think about it: STRING_SPLIT does not GUARANTEED the order of the result, which means that by splitting the text and re-concatenate it, you might get different order

According to these limitations and your requirements, please check this solution:

WITH WriterTbl AS (
    SELECT 'Sabao Fulano, XX, Sapato Feio, XX, Jose Perreira, XX' AS Writer UNION ALL
    SELECT 'Toze Jose, XX' UNION ALL
    SELECT 'Feijao Mauricio, XX Article 3'
),
MyCTE AS(
    select MyGroup = ROW_NUMBER() OVER (order by (select null)), t1.Writer
    from WriterTbl t1
)
SELECT STRING_AGG(t2.[value], ',') 
FROM MyCTE t1
CROSS APPLY (
    SELECT * from STRING_SPLIT (t1.Writer, ',') t2
) t2
where not t2.[value] like ' XX%'
group by MyGroup

Note! this task is NOT recommended, and I highly recommend you to re-think about your requirements and your architecture!

Sub-Note! Even if you stay with the need to get this result from this input, then you should probably create your own function and not use the built-in function STRING_SPLIT. There is no reason to split the data in order to concatenate it back. In your User Function you can simple remove the the text that start with ", XX" and close with "," or if this is the last part of the value. The solution here is according to your requirement to use STRING_SPLIT

Javascript switch statement fails on split string

There must be a problem with your arguments array, because the below example shows that switch works fine on a string ":=":

http://codepen.io/DeividasK/pen/xqKNWr

Your issue can be solved by mapping the array and removing any whitespace. This can be done using array = array.map(item => { return item.trim() }). Important: array.map does not modify the original array, but returns a new array with modified values.

How to split a string while ignoring the case of the delimiter?

There's no easy way to accomplish this using string.Split. (Well, except for specifying all the permutations of the split string for each char lower/upper case in an array - not very elegant I think you'll agree.)

However, Regex.Split should do the job quite nicely.

Example:

var parts = Regex.Split(input, "aa", RegexOptions.IgnoreCase);

Split a string at uppercase letters

Unfortunately it's not possible to split on a zero-width match in Python. But you can use re.findall instead:

>>> import re
>>> re.findall('[A-Z][^A-Z]*', 'TheLongAndWindingRoad')
['The', 'Long', 'And', 'Winding', 'Road']
>>> re.findall('[A-Z][^A-Z]*', 'ABC')
['A', 'B', 'C']

How do I split a string in Java?

Use the appropriately named method String#split().

String string = "004-034556";
String[] parts = string.split("-");
String part1 = parts[0]; // 004
String part2 = parts[1]; // 034556

Note that split's argument is assumed to be a regular expression, so remember to escape special characters if necessary.

there are 12 characters with special meanings: the backslash \, the caret ^, the dollar sign $, the period or dot ., the vertical bar or pipe symbol |, the question mark ?, the asterisk or star *, the plus sign +, the opening parenthesis (, the closing parenthesis ), and the opening square bracket [, the opening curly brace {, These special characters are often called "metacharacters".

For instance, to split on a period/dot . (which means "any character" in regex), use either backslash \ to escape the individual special character like so split("\\."), or use character class [] to represent literal character(s) like so split("[.]"), or use Pattern#quote() to escape the entire string like so split(Pattern.quote(".")).

String[] parts = string.split(Pattern.quote(".")); // Split on the exact string.

To test beforehand if the string contains certain character(s), just use String#contains().

if (string.contains("-")) {
    // Split it.
} else {
    throw new IllegalArgumentException("String " + string + " does not contain -");
}

Note, this does not take a regular expression. For that, use String#matches() instead.

If you'd like to retain the split character in the resulting parts, then make use of positive lookaround. In case you want to have the split character to end up in left hand side, use positive lookbehind by prefixing ?<= group on the pattern.

String string = "004-034556";
String[] parts = string.split("(?<=-)");
String part1 = parts[0]; // 004-
String part2 = parts[1]; // 034556

In case you want to have the split character to end up in right hand side, use positive lookahead by prefixing ?= group on the pattern.

String string = "004-034556";
String[] parts = string.split("(?=-)");
String part1 = parts[0]; // 004
String part2 = parts[1]; // -034556

If you'd like to limit the number of resulting parts, then you can supply the desired number as 2nd argument of split() method.

String string = "004-034556-42";
String[] parts = string.split("-", 2);
String part1 = parts[0]; // 004
String part2 = parts[1]; // 034556-42

How to use numbers split with comma in case when clause in SQL Server

You may try this...

In SQL Server below 2012:-

    DECLARE @string NVARCHAR(100) = '12,14,15,1,2'
    ;WITH cte AS
    (
       SELECT 
          CAST('<XMLRoot><RowData>' + REPLACE(t.val,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
          FROM (SELECT @string) AS t(val)
    ),
    ct as (
    SELECT 
        m.n.value('.[1]','varchar(8000)') as col
    FROM cte
    CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
    ),
    ctfinal as (
          SELECT CASE WHEN CAST(col AS int) IN ( 1,2) THEN 1 ELSE 0 END AS RES FROM ct 
    )
    SELECT TOP 1 FROM ctfinal ORDER BY RES DESC

or

    declare @Class varchar(20) 
    set @Class = '4,6,8,1'

    select 
        case when (@Class like '%1%' or @Class like '%2%')
                then 1 
                else 0 
        end

For SQL Server 2012 and above:-

    declare @Class varchar(20) 
    set @Class = '4,6,8,1'

    ; with cte as (
    select case when value in (1, 2) 
    then 1 else 0 end as res 
    from STRING_SPLIT ( @Class , ',' )
    ) 
    select top 1 res from cte order by res desc

For comparing your int value, first you need to bifurcate your string into individual int values in rows. Then only you may check them in given condition.

Although this in (1,2) is still static in this query.

Java: Split string when an uppercase letter is found

You may use a regexp with zero-width positive lookahead - it finds uppercase letters but doesn't include them into delimiter:

String s = "thisIsMyString";
String[] r = s.split("(?=\\p{Upper})");

Y(?=X) matches Y followed by X, but doesn't include X into match. So (?=\\p{Upper}) matches an empty sequence followed by a uppercase letter, and split uses it as a delimiter.

See javadoc for more info on Java regexp syntax.

EDIT: By the way, it doesn't work with thisIsMyÜberString. For non-ASCII uppercase letters you need a Unicode uppercase character class instead of POSIX one:

String[] r = s.split("(?=\\p{Lu})");

---

Related Topics