Solved

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

Posted on 2013-11-01
4
339 Views
Last Modified: 2013-11-01
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!
0
Comment
Question by:hbrady
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39616838
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
 
LVL 15

Accepted Solution

by:
gplana earned 250 total points
ID: 39616936
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
 
LVL 15

Expert Comment

by:gplana
ID: 39616939
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
 
LVL 1

Author Closing Comment

by:hbrady
ID: 39617156
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Stored Proc - Performance Enhancement 15 80
sql update 2 39
online  environment for testing sql queries 5 33
Need quicker response from an Execption table 11 25
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question