June 25, 2020 at 10:12 pm
I am trying to write select statement for a column value that looks like this: "PQ5|1|896^^^^57" where 896 and 57 change. They will be numbers only. Also, initial few chars wont change. There could be 3 or 4 Caret symbols. I need only results that have 4 carets and not 3 carets.
And where clause looks like this:
where col like '%PQ5|1|[0-9][^][^][^][^]%' - Picks up rows with 3 carets.
Where col like '%PQ5|1|[0-9]\^\^\^\^%' escape '\' - No Results.
Can someone help me this. Cant Understand what SQL Server is doing.
June 25, 2020 at 11:31 pm
Assuming you want to parse out the data - you could use CHARINDEX to find the start positions of each segment and then use SUBSTRING to get the individual values. If you only need to check the values then you just need to form the LIKE statement correctly:
Declare @testTable Table (TestValue varchar(20));
Insert Into @testTable (TestValue)
Values ('PQ5|1|896^^^^57')
, ('PQ5|1|911^^^^53')
, ('PQ5|1|201^^^27')
, ('PQ6|1|896^^^^57');
Select *
From @testTable tt
Cross Apply (Values (charindex('|', tt.TestValue, 1))) As p1(pos)
Cross Apply (Values (charindex('|', tt.TestValue, p1.pos + 1))) As p2(pos)
Cross Apply (Values (substring(tt.TestValue, 1, p2.pos - 1))) As s1(SubValue)
Cross Apply (Values (substring(tt.TestValue, p2.pos + 1, len(tt.TestValue) - p2.pos - 1))) As s2(SubValue)
Where s1.SubValue = 'PQ5|1'
And s2.SubValue Like '%^^^^%';
Select *
From @testTable tt
Where tt.TestValue Like '%PQ5|1|%^^^^%';
For future reference - it is much easier to get an answer if you provide a sample table with sample data and expected results. I have shown one way to provide that data, you can find more at the link in my signature.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 25, 2020 at 11:48 pm
Hi, column value I provided has more content to left and right of the string I have provided. So, can't use charindex. Need to use regex. regex doesnot identify ^^^^. That's where i am struck.
June 25, 2020 at 11:58 pm
Please provide sample data that shows the full data and the expected results.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 26, 2020 at 12:17 am
if the pattern is "PQ5|1|896^^^^57", where "896" is three-digit(ddd), "57" is two-digit(dd), this also works:
where col like '%PQ[0-9]|[0-9]|[0-9][0-9][0-9]^^^^[0-9][0-9]%'
June 26, 2020 at 12:21 am
But isn't ^ wild character for like and regular expression.
June 26, 2020 at 12:42 am
But isn't ^ wild character for like and regular expression.
No, it is not a wildcard and like does not use regular expressions.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 26, 2020 at 10:58 am
I just tested that and I think problem is with the [0-9].
Like clause is "%PQ5|1|896^^^^%" - Picks up row1.
Like Clause is "%PQ5|1|[0-9]^^^^%" - Picks up nothing. Should have picked up rows 1,2, 3, 4
Can someone advice pls.
June 26, 2020 at 2:05 pm
The code [0-9] searches for EXACTLY ONE DIGIT. Your examples have 2-4 digits in that position, which is not exactly one, so they don't match.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 26, 2020 at 2:23 pm
Oh, if I only know that its a number and dont know the size, how do I do it?
Very confusing for me. Cant find proper documentation.
June 26, 2020 at 5:00 pm
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql?view=sql-server-ver15
Did you even try my solutions? The second one definitely works as you expect...
Select *
From @testTable tt
Where tt.TestValue Like '%PQ5|1|%^^^^%';
And if you could provide some real world examples - it would be much easier to provide a working example.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 26, 2020 at 5:09 pm
srisadhu wrote:But isn't ^ wild character for like and regular expression.
No, it is not a wildcard and like does not use regular expressions.
LIKE absolutely does use regular expressions. It's just not a very powerful regex language. While things like quantification (?, *, {m,n}) are typical in regex languages, they are not necessary to be considered a regex language.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 26, 2020 at 5:47 pm
Post deleted because the op seemed offended by advice on how to post such problems.,
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2020 at 6:14 pm
Just posting some solution that you can think of doensot make you pro and doesnot make ignorant.
When someone posts on forum it means that they can't get through documentation(which you have agreed yourself).
I have posted enough data for finding a pattern and I can figure out something myself. Thanks.
June 26, 2020 at 7:06 pm
Just posting some solution that you can think of doensot make you pro and doesnot make ignorant.
When someone posts on forum it means that they can't get through documentation(which you have agreed yourself).
I have posted enough data for finding a pattern and I can figure out something myself. Thanks.
I'm making suggestions to you on how you can get better answers more quickly. But, no problem... I've deleted my post so you can figure out something yourself.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply