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 |
|
v_kash
Starting Member
45 Posts |
 Posted - 2009-10-29 : 15:47:18
|
| Hello - I have a quick question. I have a column called DIST_NAME. lets say these are the two values in the column..IBS ABILENE (DP-244566)IBS AKRON CANTON (DP-206835)What I want to see is IBS ABILENE, and IBS AKRON CANTON. What's the best way to get rid of the parantheses and everything in it? I tried a replace function, but could'nt get it to work. Any help would be greatly appreciated.Thanks |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-10-29 : 15:51:24
|
here's one wayselect left(DIST_NAME,charindex('(',DIST_NAME)-1 ) from <urtable> |
 |
|
|
v_kash
Starting Member
45 Posts |
Posted - 2009-10-29 : 15:55:29
|
| Vijay - this is the msg I got when tryin that solution -Invalid length parameter passed to the SUBSTRING function.Thanks! |
|
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-10-29 : 15:59:08
|
| [code]declare @t table (a varchar(100))insert into @t (a)select 'IBS ABILENE (DP-244566)' unionselect 'IBS AKRON CANTON (DP-206835)'select substring(a,0,charindex('(', a)-1) from @t[/code]The left function should work also.Some days you're the dog, and some days you're the fire hydrant. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-10-29 : 16:11:47
|
Ok..some of your values may not have the '('..thats y you get the error...handle it with a case as below..select case when charindex('(',DIST_NAME) > 0 then left(DIST_NAME,charindex('(',DIST_NAME)-1 ) else DIST_NAME end as new_field from <urtable>quote:
Originally posted by v_kash
Vijay - this is the msg I got when tryin that solution -Invalid length parameter passed to the SUBSTRING function.Thanks!
|
|
|
|
v_kash
Starting Member
45 Posts |
Posted - 2009-10-29 : 16:24:55
|
| thanks my brutha!! |
|
|
|
|
|
|
REPLACE FUNCTION - NEED HELP