Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

need sql Query in sqlserver2008 or 2005

Posted on 2010-11-12
8
Medium Priority
?
579 Views
Last Modified: 2012-05-10
hi ,

i have table called EMP.
tHE EMP TABLE CONTAINS  10 EMP NAMES LIKE SHOWN BELOW.
I NEED THE QUERY FOR TO GET
REQUIREMENT1.
--------------------
THE FIRST FIVE EMPNAMES NEED TO COME IN UPPERCASE AND
THE REST FIVE EMPNAMES IN LOWER CASE.
PLS PROVIDE ME THE QUERY FOR THE ABOVE.

REQUIREMENT2
-------------------
tHE EMP TABLE CONTAINS 10 RECORDS.FOR EX
THE EMP NAME CONTAINS RAVIKUMAR AS EMPNAME.
I NEED THE FIRST 4 CHARACTERS IN UPPERCASE AND REST 5 CHARACTERS IN LOWERCASE
WHICH IS SHOWN BELOW.

EMPNAME
-------------
RAVIkumar
ANANDkumar.

pls provide me the seperate quries for the 2 requirements.


Thanks
Ravi


0
Comment
Question by:mu_ravi1
[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
8 Comments
 
LVL 6

Expert Comment

by:DalHorinek
ID: 34126846
Requirement 1:

-- it assumes that it has some primary key called id

(SELECT UPPER(name) FROM emp ORDER BY id LIMIT 5)
   UNION ALL
(SELECT LOWER(name) FROM emp ORDER BY id DESC LIMIT 5);
-- but it changes order, if you're sure there's only 10 records in table, you use:
(SELECT UPPER(name) FROM emp LIMIT 5)
   UNION ALL
(SELECT LOWER(name) FROM emp LIMIT 5,5);

Requirement 2:

SELECT CONCAT(UPPER(SUBSTR(name, 1,4)), LOWER(SUBSTR(name, 5,CHAR_LENGTH(name)-4))) FROM emp
0
 
LVL 6

Expert Comment

by:DalHorinek
ID: 34126861
Actually that 2nd wouldn't work probably on MS, I didn't notice that.

SELECT UPPER(SUBSTRING(name, 1,4)) + " " + LOWER(SUBSTRING(name, 5,LEN(name)-4)) FROM emp

This should be correct.
0
 

Author Comment

by:mu_ravi1
ID: 34126962
hi DalHorinek

Requirement1 query
The table contains more the 10 records

(SELECT UPPER(name) FROM emp ORDER BY id LIMIT 5)
   UNION ALL
(SELECT LOWER(name) FROM emp ORDER BY id DESC LIMIT 5);

This query given not executed.
it is giving the below error

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ORDER'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'ORDER'.
-----------------------------------------------------------------------------
Requirement 2
SELECT UPPER(SUBSTRING(name, 1,4)) + " " + LOWER(SUBSTRING(name, 5,LEN(name)-4)) FROM emp

This query given not executed.
it is giving the below error

Invalid length parameter passed to the SUBSTRING function.

pls help me
Ravi

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:mu_ravi1
ID: 34126972
Hi DalHorinek
FYI
iam using sqlserver 2005.
regards
ravi
0
 
LVL 7

Expert Comment

by:rmm2001
ID: 34128716
#1 I'm confused..
do you want it like this?
JAMES
ADAM
SAM
LUKE
JON
jane
laura
joan
emily
sara
TOM
DON
...



#2
SELECT UPPER(SUBSTRING(name, 1,4)) + ' ' + LOWER(RIGHT(LEFT(name,4), LEN(name)-4)) FROM emp
0
 
LVL 13

Accepted Solution

by:
devlab2012 earned 1000 total points
ID: 34136629
#1
--first five UPPER, next five lower, other records unaffected
SELECT CASE WHEN RowNum <=5 then Upper(Name) WHEN RowNum <=10 then Lower(Name) else Name end Name
FROM(
      select Name, Row_Number() over(order by Name) RowNum from emp
) t

--returns only first 10 records, first five UPPER, next five lower
SELECT CASE WHEN RowNum <=5 then Upper(Name) WHEN RowNum <=10 then Lower(Name) else Name end Name
FROM(
      select top 10 Name, Row_Number() over(order by Name) RowNum from emp
) t


Requirement 2
I see that RAVI and ANAND are like first name of person. You want to make it upper case. But there is no way that sql can detect by itself that this portion of a string is first name. You can only convert a fixed length to upper case. Following query will do this-
SELECT CASE WHEN LEN(Name) >= 4 THEN UPPER(SUBSTRING(Name, 1,4)) + LOWER(SUBSTRING(Name,5, LEN(Name)-4))
ELSE UPPER(Name) END Name
FROM Employee

0
 

Author Comment

by:mu_ravi1
ID: 34142612
Hi DalHorinek

Thank u very much for ur concern.
now the quries are working fine.

one small modification in the first reqiriemnt.

#1
--first five UPPER, next five lower, other records unaffected
SELECT CASE WHEN RowNum <=5 then Upper(Name) WHEN RowNum <=10 then Lower(Name) else Name end Name
FROM(
      select Name, Row_Number() over(order by Name) RowNum from emp
) t

in the above mentioned

i need the query for n number of records not fixed 10 records.
The table may have n number of records.
could u pls change the query for 'n' number of records.
Sorry for the late reply i am in vacation.
Ravi
0
 

Author Closing Comment

by:mu_ravi1
ID: 34143805
Excellent
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

636 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