Select records based on character count to the left and right of a given character

I need to return material_id and material_descrip from a table called mmaster based on selection criteria of data in  a column called user_1.  

I need to select records where the vale in user_1 contains a ‘-‘  AND the count of characters to the left of the ‘-‘ = 5 AND the count of the characters to the right of ‘-‘ =2.  

Thanks in advance!
LVL 1
hbradyAsked:
Who is Participating?
 
gplanaConnect With a Mentor Commented:
I think what you want is just this:
SELECT material_id, material_descrip
FROM mmaster
WHERE user_1 LIKE '_____-__';

Open in new window


The LIKE operator returns rows that matches the string with some special chars that represents special meaning. The underscore ( _ ) char means "a single character goes here, but it could be any character". Also the percentage character ( % ) means any set of characters, but you don't need this.

So you want any record that user_1 has a value of _ _ _ _ _ - _ _ which means any-character any_character any-character any-character any-character, a hyphen, any-character, any-character. So exactly 5 characters of any kind, then a hyphen character, and then two more characters of any kind.

Try it and let me know ;)
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl in SSMS..
Declare @str varchar(10) = '12345-67'

IF CHARINDEX ('-', @str,  1) = 6
	SELECT 'There are five characters before the dash'

IF LEN(@str) - CHARINDEX ('-', @str,  1)= 2 
	SELECT 'There are two characters after the dash'

IF CHARINDEX ('-', @str,  1) > 0
	begin
	SELECT LEFT(@str, CHARINDEX ('-', @str,  1) - 1) as characters_left_of_the_dash
	SELECT RIGHT(@str, LEN(@str) - CHARINDEX ('-', @str,  1)) as characters_right_of_the_dash
	end

Open in new window

0
 
gplanaCommented:
Please look at this link from Microsoft if you want to know more about LIKE operator:
http://technet.microsoft.com/en-us/library/ms174473.aspx
0
 
hbradyAuthor Commented:
Two good solutions, one simple, one a bit more complex.  I actually ran the simple one and it gave me what I needed; however, it picked up a few records which contained multiple hyphens.  Easy enough to omit but were this a large data set I would have gone with the more complex solution.  Thank you both!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.