mu_ravi1
asked on
need sql Query in sqlserver2008 or 2005
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
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
Last Comment
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.
SELECT UPPER(SUBSTRING(name, 1,4)) + " " + LOWER(SUBSTRING(name, 5,LEN(name)-4)) FROM emp
This should be correct.
ASKER
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
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
ASKER
Hi DalHorinek
FYI
iam using sqlserver 2005.
regards
ravi
FYI
iam using sqlserver 2005.
regards
ravi
#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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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
ASKER
Excellent
MySQL Server
MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.
49K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
-- 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