CODEWITHSUNDEEP
sqlsql-serversubstringcasecharindex
SQL_Aliases
SQL_Aliases

Reputation: 1

I am trying to get the start date ONLY from the column below using a SUBSTRING. Can someone tell me what I'm doing wrong?

enter image description here

This is what I have so far:

DECLARE @STR AS VARCHAR(1000) = 'CustomerID #101010 added. Fixed Price: 3.5555 Effective from 10/16/2024 to 03/31/2 SADFGZFGDA025 Deliveries: 100 Company Pricing Program: TEST 1 Pricing Plan Name: Test plan' 

SELECT 
     CASE 
         WHEN CHARINDEX('Effective from ', @STR) > 0 
              AND CHARINDEX(CHAR(10), @STR, CHARINDEX('Effective from ', @STR)) > 0 
             THEN SUBSTRING(@STR, CHARINDEX('Effective from ', @STR) + LEN('Effective from ') + 1, CHARINDEX(CHAR(10), @STR, CHARINDEX('Effective from ', @STR) + LEN('Effective from ') + 1) - (CHARINDEX('Effective from ', @STR) + LEN('Effective from ') + 1)) 
          WHEN CHARINDEX('Effective from ', @STR) > 0 
              THEN SUBSTRING(@STR,CHARINDEX('Effective from ', @STR) + LEN('Effective from ') + 1, 60) 
          ELSE NULL 
     END 'Start Date'

I'm only trying to see 10/16/2024, so I need the substring to end before the to.

Upvotes: -3

Views: 99

Answers (4)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22311

I am trying to get the start date ONLY from the column below using a SUBSTRING.

Assuming that data resides in the database table column, here is another way to handle it via tokenization through XML and XQuery.

Notable points:

  • CROSS APPLY is tokenizing column data as XML.
  • XPath expression [contains(., "/")][1] is looking for the first data element with a forward slash, i.e. fromDate
  • TRY_CONVERT(DATE,...) is producing fromDate as a real DATE data type.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, tokens VARCHAR(1000));
INSERT INTO @tbl (tokens) VALUES
('CustomerID #101010 added. Fixed Price: 3.5555 Effective from 10/16/2024 to 03/31/2 SADFGZFGDA025 Deliveries: 100 Company Pricing Program: TEST 1 Pricing Plan Name: Test plan');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1);

SELECT * 
    , TRY_CONVERT(DATE, c.query('
        for $x in /root/r/text()[contains(., "/")][1]
        return $x
    ').value('.', 'CHAR(10)'), 101) AS startDate
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
      REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
      ']]></r></root>' AS XML)) AS t1(c);

Output

id tokens startDate
1 CustomerID #101010 added. Fixed Price: 3.5555 Effective from 10/16/2024 to 03/31/2 SADFGZFGDA025 Deliveries: 100 Company Pricing Program: TEST 1 Pricing Plan Name: Test plan 2024-10-16

Upvotes: 0

T N
T N

Reputation: 10204

You can reduce the number of repeated expressions by using CROSS APPLY to calculate intermediate results. This allows you to calculate a value once and use it multiple times later in the query. Defining the start and end search strings as variables can also improve readability.

Generalizing the use case to string values coming from a table, the following should achieve your desired results.

DECLARE @StringStart VARCHAR(100) = 'Effective from '
DECLARE @StringStartLen INT = LEN(@StringStart + 'x') - 1 -- True length w/ space
DECLARE @StringEnd1 VARCHAR(100) = ' '
DECLARE @StringEnd2 VARCHAR(100) = CHAR(10)

SELECT D.Str, R.Result
FROM Data D
CROSS APPLY(
    SELECT NULLIF(CHARINDEX(@StringStart, D.Str), 0) + @StringStartLen AS StartPos
) P1
CROSS APPLY (
    SELECT LEAST(
        NULLIF(CHARINDEX(@StringEnd1, D.Str, P1.StartPos), 0),
        NULLIF(CHARINDEX(@StringEnd2, D.Str, P1.StartPos), 0),
        LEN(D.Str) + 1
        ) - P1.StartPos AS Length
) P2
CROSS APPLY (
    SELECT TRY_CAST(SUBSTRING(D.Str, P1.StartPos, P2.Length) AS Date) AS Result
) R

NULLIF() is used to map the no-match 0 result from CHARINDEX() to null, so that non-matches will be ignored.

Another approach is to use PATINDEX() to search for the combined prefix, content, and suffix using a SQL Server LIKE pattern.

DECLARE @StringPattern VARCHAR(100) =
    '%Effective from [0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9][ ' + CHAR(10) + ']%'
DECLARE @StringStartOffset INT = 15
DECLARE @ExtractLength INT = 10

SELECT
    D.Str,
    TRY_CAST(SUBSTRING(
        D.Str,
        NULLIF(PATINDEX(@StringPattern, D.Str + ' '), 0) + @StringStartOffset,
        @ExtractLength
        ) AS Date) AS Result
FROM Data D

This assumes that your dates are always of the form "MM/DD/YYYY" with leading zeros. The end of the pattern matches either a space or a newline. To allow end-of-string matching, a space is added to the source string.

The TRY_CAST() also assumes that your dates are compatible with the current SET DATEFORMAT setting. You can alternately use TRY_CONVERT() with an explicit format code (such as 101 or 103) or remove the conversion altogether to just return the text.

Results (with some modified sample data)

Str Result
Case with space - Effective from 10/16/2024 to ... 2024-10-16
Case with newline - Effective from 10/16/2024
more stuff ...		 2024-10-16
Case at end of string - Effective from 10/16/2024 2024-10-16
Not matched (: instead of space) - Effective from:10/16/2024 null

See this db<>fiddle for a demo.

Upvotes: 1

Thom A
Thom A

Reputation: 95949

It really feels like you have overly complicated the problem here. Your date is only 10 characters, so why not just find where the position of the date is, and then get the 10 characters after that? Something like this:

DECLARE @STR AS varchar(1000) = 'CustomerID #101010 added. Fixed Price: 3.5555 Effective from 10/16/2024 to 03/31/2 SADFGZFGDA025 Deliveries: 100 Company Pricing Program: TEST 1 Pricing Plan Name: Test plan';

SELECT SUBSTRING(@STR,NULLIF(CHARINDEX('Effective from',@STR),0)+LEN('Effective from')+1,10);

So I find the position of 'Effective from' (if it's not found, return NULL), add the length of the string 'Effective from'+1, and then return the next 10 characters.

You may, as well, want to wrap the entire expression in a TRY_CONVERT to attempt to convert the value to an actual date value. This also means you don't get completely invalid dates if the text for the "date" is nonsense, such as the "to" date in your example, which is 03/31/2 (and thus not a valid date):

SELECT TRY_CONVERT(date, <expression>, 101);

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40359

Firstly, you have code like

CHARINDEX(CHAR(10), @STR)

In several places. While it (curiously) does not raise an error, that first parameter is not a particularly valid string to search for, so that function call will always return 0. This means your first WHEN Statement will always be ignored.

The second WHEN is structured to start extracting characters from the correct point in the string, but then it reads an arbitrary 60 characters, as opposed to what you actually want.

The following shows one way to extract the desired value. I broke it into two parts for clarity—certainly it could all be munged together into a single statement, but things like this get hard to read very quickly. Note that this does not factor in error handling—it assumes that there will be a valid date there, which I would not count on given the “provided “to” date value shown.

DECLARE @STR AS VARCHAR(1000) = 'CustomerID #101010 added. Fixed Price: 3.5555 Effective from 10/16/2024 to 03/31/2 SADFGZFGDA025 Deliveries: 100 Company Pricing Program: TEST 1 Pricing Plan Name: Test plan' 

DECLARE
  @SubString1     varchar(1000)
 ,@EffectiveFrom  date

--  The string, starting at the date to be extracted
SET @SubString1 = SUBSTRING(@STR, CHARINDEX('Effective from ', @STR) + 15, 1000)

--PRINT @SubString1
--PRINT charindex(' to ', @Substring1)

--  Extract the date
SET @EffectiveFrom = left(@Substring1, charindex(' to ', @Substring1))

PRINT @EffectiveFrom

Upvotes: 0

Related Questions