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.
| Author |
Topic |
|
srireddyp
Starting Member
8 Posts |
 Posted - 2006-06-05 : 15:07:45
|
| Hi,I have a table called employee with enumber,ename columnsand following dataenumber ename12321-001 Mike12322-002 Tom12333-003 Kevin23232SP MattI need a sql statement to find the enumberbefore "-" and before any alphabet. Here 12321, 12322, 12333, 23232 etcHow can I do this ?Thanks for helpSrini |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-06-05 : 16:33:48
|
| Not sure if this is what you're looking for but acording to your example it will work:SELECT LEFT(enumber, 5) AS Something, ename FROM table--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
 |
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2006-06-05 : 21:36:27
|
| create table #temp( enumber varchar(20), ename varchar(20))goinsert into #temp values('12321-001','Mike')insert into #temp values('12322-002','Tom')insert into #temp values('12333-003','Kevin')insert into #temp values('23232SP','Matt')insert into #temp values('2451','John')GOSELECT CASE WHEN CHARINDEX('-',enumber)>0 THEN SUBSTRING(enumber,1, CHARINDEX('-',enumber)-1) WHEN PATINDEX('%[^0-9]%',enumber)>0 THEN SUBSTRING(enumber,1, PATINDEX('%[^0-9]%',enumber)-1) ELSE enumber END AS ENUMBER, ENAMEFROM #tempGOdrop table #tempMay the Almighty God bless us all! |
|
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2006-06-05 : 21:37:57
|
| Actually, you can remove the CHARINDEX, the patindex will suffice.May the Almighty God bless us all! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Sql Statement