December 15, 2017 at 6:46 am
Why in God's name does this not work? expecting "dateFirst":
DECLARE @searchString varchar(1500)
SET @searchString='03/09/2017 07:52 AM | Lastname, Firstname | Completed - irrelevant note.'
SELECT CASE WHEN @searchString LIKE '[0-9][0-9]/[0-9][0-9]/201[0-9] % [AP]M \|%' THEN 'dateFirst' ELSE 'somethingElse' END
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
December 15, 2017 at 6:50 am
Your pattern requires two spaces between the date and the time, and your value only has one space.
Also, you didn't explicitly specify the \ as the escape character:
SELECT CASE WHEN @searchString LIKE '[0-9][0-9]/[0-9][0-9]/201[0-9] %[AP]M \|%' ESCAPE '\'
THEN 'dateFirst' ELSE 'somethingElse' END
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 15, 2017 at 6:54 am
The spaces are not the issue, it's <date><space><time><space><[AP]M><space><pipe>, but declaring the escape character seems to be the issue. Thank you!
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
December 15, 2017 at 7:40 am
Why would you try to escape the pipe? It's not a wildcard in t-sql.
December 15, 2017 at 8:00 am
jonathan.crawford - Friday, December 15, 2017 6:54 AMThe spaces are not the issue, it's <date><space><time><space><[AP]M><space><pipe>, but declaring the escape character seems to be the issue. Thank you!
Your pattern requires two spaces before the AM/PM.
LIKE '[0-9][0-9]/[0-9][0-9]/201[0-9] % [AP]M...
......................................................*.*
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 15, 2017 at 8:06 am
DECLARE @searchString VARCHAR(1500);
SET @searchString = '03/09/2017 07:52 AM | Lastname, Firstname | Completed - irrelevant note.';
SELECT CASE
WHEN @searchString LIKE '[0-9][0-9]/[0-9][0-9]/201[0-9]%[AP]M%|%'
THEN 'dateFirst'
ELSE 'somethingElse'
END;
DECLARE @searchString VARCHAR(1500);
SET @searchString = 'some text | 03/09/2017 07:52 AM |Lastname, Firstname | Completed - irrelevant note.';
SELECT CASE
WHEN @searchString LIKE '[0-9][0-9]/[0-9][0-9]/201[0-9]%[AP]M%|%'
THEN 'dateFirst'
ELSE 'somethingElse'
END;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
December 15, 2017 at 8:09 am
What about avoiding the pattern match and check for valid date values?
DECLARE @searchString varchar(1500);
SET @searchString='03/09/2017 07:52 AM | Lastname, Firstname | Completed - irrelevant note.';
SELECT CASE WHEN TRY_CONVERT( datetime, SUBSTRING( @searchString, 0, CHARINDEX('|', @searchString))) IS NOT NULL THEN 'dateFirst' ELSE 'somethingElse' END;
December 18, 2017 at 8:08 pm
ScottPletcher - Friday, December 15, 2017 8:00 AMjonathan.crawford - Friday, December 15, 2017 6:54 AMThe spaces are not the issue, it's <date><space><time><space><[AP]M><space><pipe>, but declaring the escape character seems to be the issue. Thank you!Your pattern requires two spaces before the AM/PM.
LIKE '[0-9][0-9]/[0-9][0-9]/201[0-9] % [AP]M...
......................................................*.*
Exactly, one before the hh:mm and one after the hh:mm, as the example provided. Spaces are not the concern, but thank you.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
December 18, 2017 at 8:14 pm
Luis Cazares - Friday, December 15, 2017 8:09 AMWhat about avoiding the pattern match and check for valid date values?
DECLARE @searchString varchar(1500);SET @searchString='03/09/2017 07:52 AM | Lastname, Firstname | Completed - irrelevant note.';
SELECT CASE WHEN TRY_CONVERT( datetime, SUBSTRING( @searchString, 0, CHARINDEX('|', @searchString))) IS NOT NULL THEN 'dateFirst' ELSE 'somethingElse' END;
well, mostly because I didn't know about that function. Thanks, Luis! Also, because I am parsing a large extremely variable text field and was taking it one step at a time for troubleshooting (chop out the string and update as field value in a temp table, so if it dies I can see what I actually tried to convert and correct my substringyness)
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
December 18, 2017 at 8:17 pm
Luis Cazares - Friday, December 15, 2017 7:40 AMWhy would you try to escape the pipe? It's not a wildcard in t-sql.
isn't it an indicator of OR? https://docs.microsoft.com/en-us/sql/relational-databases/scripting/search-text-with-regular-expressions
*Edit, can't spell
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
December 18, 2017 at 8:19 pm
J Livingston SQL - Friday, December 15, 2017 8:06 AMDECLARE @searchString VARCHAR(1500);
SET @searchString = '03/09/2017 07:52 AM | Lastname, Firstname | Completed - irrelevant note.';
SELECT CASE
WHEN @searchString LIKE '[0-9][0-9]/[0-9][0-9]/201[0-9]%[AP]M%|%'
THEN 'dateFirst'
ELSE 'somethingElse'
END;DECLARE @searchString VARCHAR(1500);
SET @searchString = 'some text | 03/09/2017 07:52 AM |Lastname, Firstname | Completed - irrelevant note.';
SELECT CASE
WHEN @searchString LIKE '[0-9][0-9]/[0-9][0-9]/201[0-9]%[AP]M%|%'
THEN 'dateFirst'
ELSE 'somethingElse'
END;
Yes, but I was including the spaces because I was concerned that just using the % wildcard would capture anything with an entirely different timestamp later and still count it. Which sounds so ridiculous when I type it out, because one % or a space plus a % has the same effect. So, um, yep.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
December 18, 2017 at 8:26 pm
jonathan.crawford - Monday, December 18, 2017 8:17 PMLuis Cazares - Friday, December 15, 2017 7:40 AMWhy would you try to escape the pipe? It's not a wildcard in t-sql.isn't it an indicator of OR? https://docs.microsoft.com/en-us/sql/relational-databases/scripting/search-text-with-regular-expressions
*Edit, can't spell
In regular expressions, yes. In T-SQL, no. LIKE operator uses a very limited set of wildcards.
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply