Help: Retrieving specific information from SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help: Retrieving specific information from SQL

Author

Topic

Live24x7
Starting Member

2 Posts

Posted - 2011-11-21 : 06:28:08

I am pretty new to SQL. Here's my requirement

My SQL table1 contains, amongst other fields a Six digit number in say a field named pincode.

How do i write a SQL SELECT FROM WHERE query to display all results where the first 4 digits match for a variable input of 6 digits.

So suppose a guy enters 100018
it should retrieve all matching fields entries ranging from

100001 to 100099

Pls Pls. Help me out.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 06:37:41

[code]SELECT columns... FROM table WHERE field LIKE @searchcriteria + '[0-9][1-9]'[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Live24x7
Starting Member

2 Posts

Posted - 2011-11-21 : 06:51:53

Thanks Visakh

what would be @searchcriteria for fetching only the first 4 digits before appending '[0-9][1-9]'
How would that be worded ?

sorry am a total noob.

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-11-21 : 07:37:51

I thought that you can get the correct search criteria by using
fEdit: To explain, RIGHT takes 2 arguments - the string (or column) to operate on, and the number of characters to return (starting at the "right" side of the string). LEN returns the length of the column data, and we subtract four so that our RIGHT function leaves the leftmost 4 characters "behind".

Hope this makes sense.

Edit again - I just read Andrew's response, and he may very well have interperpereted correctly, and I might be mistaken. If this is the case (and you want to UPDATE the table rather than just return doctored results), you can do this:

UPDATE MyTable
SET MyColumn = RIGHT(MyColumn, LEN(MyColumn) - 4)

He's on the right track, but his solution will keep the 4 characters at the start of the string, rather than discarding said 4 characters.

also you can use substring

SET code2 = SUBSTRING(Code1, 5, LEN(CODE1) -4)

The SUBSTRING function trims down Code1, starting at the FIFTH character, and continuing for the length of
http://stackoverflow.com/questions/982819/sql-query-doubt-removing-prefix-characters

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 08:50:15

quote:
Originally posted by Live24x7

Thanks Visakh

what would be @searchcriteria for fetching only the first 4 digits before appending '[0-9][1-9]'
How would that be worded ?

sorry am a total noob.

LEFT(field,4) = @searchcriteria

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-22 : 03:19:12

Check this

CREATE TABLE #tmp(va INT)
INSERT INTO #tmp
SELECT 100000
UNION ALL
SELECT 100111
UNION ALL
SELECT 100155
UNION ALL
SELECT 100001
UNION ALL
SELECT 100098
UNION ALL
SELECT 100055

SELECT * FROM #tmp WHERE va LIKE LEFT('100018',4) + '[0-9][1-9]'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources