how do I get columns of table by patter of string in stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

I am developing stored procedure that requires matching value from table. For instance,account column in my table can have values like 2.1, 2.1.1, 2.1.2, 2.2, 3.4.5.7 and so on ( digit being separated by ".") Now I want to fetch value from this table based on pattern. Example. For fetching value 2.1 and 2.2, I used Sql query "select * from table where account like '2.%'".. this query fetched 2.1, 2.2 but also 2.1.1, 2.1.2 which I do not need..
What is the Sql query to fetch only value 2.1 and 2.2 .
Any help!!
Thanks in advance.

Posted 13-May-15 20:41pm

Codes DeCodes

Add a Solution

2 solutions

Solution 3

You can match on a single character e.g.

select * from [table] WHERE account like '2._'

or you can include in the where clause the pattern you don't want to match e.g.

SQL

select * from [table] WHERE account like '2.%'
and account not like '2.%.%'

You can do much more with PATINDEX[^] - this blog[^] goes through the various options

Posted 13-May-15 22:02pm

CHill60

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2015-05-13T22:00:00

In practice, there isn't a simple query that will do that: SQL string handling is basic at best. '%' matches any number of characters (including zero) and while there is a "match any one character": underscore (which I can't type as it triggers Markdown to "italice mode" instead of displaying it - it's a site bug) it will always match one and only one character.
So LIKE '2._' would solve the immediate problem and match "2.1" and "2.2" without matching "2.1.1" - but it won't find "2.11" either.

I'd strongly suggest that you need to do this in your presentation software, or change your DB design to make life easier for SQL to process this stuff.
You can probably do it - start by looking at this: Converting comma separated data in a column to rows for selection[^] which separates CSV style data and woudl be easy to mod to your period separated data - but then you need to work out how to use that to "limit" your selections. Not an easy job, and seriously inefficient on big tables!

I'd spend some time looking at the data and seeing if there is a better way to store it which makes this kind of query easier, now and in the future.