May 12, 2015 at 2:04 pm
Hi,
I'm using the following formula to retrieve records with one hyphen somewhere in the middle.
WHERE MyColumn LIKE '%-%' AND MyColumn NOT LIKE '%-%-%'
How can I further refine this to make sure that I am retrieving only numerical values to the left of the hyphen? I thought about using "[0-9]" in the expression, but I'm not sure how to handle this when there could be any number of values to the left of the hyphen.
Any help is greatly appreciated!
May 12, 2015 at 2:44 pm
May 13, 2015 at 7:48 am
Lynn,
Thank you for responding!
Your solution appears to work. I added a couple of sample values to further test it out.
declare @test-2 table(TestData varchar(32));
insert into @test-2
values ('1234-qwer'),('12e3-aer'),('1-12er'),('a3874-wlsie3'),('2873b-23');
select * from @test-2;
select * from @test-2 where TestData not like '%[^0-9]%-%'
The two new values I added at the end worked properly, in that they were not pulled into the SELECT, but I'm not sure I understand why. The see that with the "^", you're saying not to include records that use something other than 0-9, but that logic is surrounded by "%", which I thought meant a string of any type and amount of characters. So it seems like it should only be looking at the middle of the string prior to the hyphen rather than the beginning and end of it.
May 13, 2015 at 8:14 am
tarr94 (5/13/2015)
Lynn,Thank you for responding!
Your solution appears to work. I added a couple of sample values to further test it out.
declare @test-2 table(TestData varchar(32));
insert into @test-2
values ('1234-qwer'),('12e3-aer'),('1-12er'),('a3874-wlsie3'),('2873b-23');
select * from @test-2;
select * from @test-2 where TestData not like '%[^0-9]%-%'
The two new values I added at the end worked properly, in that they were not pulled into the SELECT, but I'm not sure I understand why. The see that with the "^", you're saying not to include records that use something other than 0-9, but that logic is surrounded by "%", which I thought meant a string of any type and amount of characters. So it seems like it should only be looking at the middle of the string prior to the hyphen rather than the beginning and end of it.
Here is what it is doing, and hopefully I explain correctly. The pattern is looking for any character except 0 through 9 in any position in the string before the hyphen. The not like then completes the filter by selecting those values that fail the pattern. For example, the first string is '1234-qwer'. There are no characters before the hyphen that aren't 0 - 9, so this value is selected.
Does that help?
May 14, 2015 at 7:29 am
But shouldn't the "%" characters on the outside of the "[^0-9]" mean that the first character of the last one before the hyphen could be anything?
May 14, 2015 at 8:35 am
tarr94 (5/14/2015)
But shouldn't the "%" characters on the outside of the "[^0-9]" mean that the first character of the last one before the hyphen could be anything?
They could be anything or nothing, meaning the beginning of the string to be searched or the end of the string to be searched. In this case the end of the string is what ever value resides immediately to the left of the hyphen.
May 14, 2015 at 12:32 pm
Lynn,
Thank you, this finally made sense to me. I keep forgetting that you used "not like" in the logic.
I appreciate your help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply