Solved

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

Posted on 2013-11-01
337 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
Question by:hbrady
• 2
4 Comments

LVL 65

Assisted Solution

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
``````
0

LVL 15

Accepted Solution

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 '_____-__';
``````

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

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

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

Question has a verified solution.

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

### Suggested Solutions

Title # Comments Views Activity
Showing random records from database 10 37
Oracle Query - Return results based on minimum value 8 32
Mysql Left Join Case 10 54
SQL Query assistance 16 25
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

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

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