how to get name only from these column of enmae from emp table?

Question

Post reply

how to get name only from these column of enmae from emp table?

raghuldrag

Hall of Fame

Points: 3160
More actions
June 17, 2013 at 1:33 am

#279275

Hai friends,
My emp Table is
create table emp
(
location varchar(20),
ename varchar(30)
)
insert into emp (location,ename) values('A','00001:ravi')
insert into emp (location,ename) values('A','00002:rahie')
insert into emp (location,ename) values('B','00003:raghul')
insert into emp (location,ename) values('B','00004:ram')
my requring output is
only name from ename column of emp table

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You can do it with substring and charindex functions:

select substring(ename,charindex(':',ename)+1,len(ename)) from emp

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Arul prakash Old Hand Points: 318 More actions · Answer 2

select substring(ename,charindex(':',ename)+1,len(ename)) from emp

The charindex start from "0".......:-)

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

Arul prakash (6/18/2013)
select substring(ename,charindex(':',ename)+1,len(ename)) from emp
The charindex start from "0".......:-)

Is your solution not the same as the one posted above yours?

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

I realize you have a solution but I would be remiss if I did not mention that you should consider separating this data permanently. You are storing two values in a single column. This violates first normal form.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 5

Sean is absolutely right. Have a read through Tom Thomson's excellent series on this site around Normal Forms starting right here[/url]. Then read the follow-up articles on Second and Third Normal Form, where you really want your database structure to be. And BTW, read the discussions attached to those articles too if you find some time.

Regards,

Jan

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]