Delete from a String - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Delete from a String

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2010-01-12 : 14:43:11

Hi.I'd like to get a part of my field which has "mah" after that.
I mean if I have these values:

4400MAH
14.8V, 6600mAh
FRU 02K6729 500mah san
14.8V,10mAh,14.8V

I need to have these results & delete the rest:
4400MAH
6600mAh
500mah
10mAh

plz help.
thanks in advanced

jcampbell
Starting Member

9 Posts

Posted - 2010-01-12 : 18:17:55

this will get rid of any characters after finding MAH, if you want to get rid of all other fields in the front of the string I will need to know what the rules are. what is the start of the string?

DECLARE @t TABLE (string varchar(40))
INSERT INTO @t
SELECT '4400MAH' UNION ALL
SELECT '14.8V, 6600mAh' UNION ALL
SELECT 'FRU 02K6729 500mah san' UNION ALL
SELECT '14.8V,10mAh,14.8V' union all
SELECT 'nostring test'

select
case when charindex('MAH',string)> 0 then
substring(string,1,charindex('MAH',string)+2)
else string
end newstring
from @t

a.ashabi
Posting Yak Master

117 Posts

Posted - 2010-01-14 : 12:26:32

thanks for yr reply.
the start of the string could be anything but there is a space before the digits next to the mah for sure
like this:
14.8V, 6600mAh
HP CE019XF1742A ,9.6V, 4000mah
I really appreciate ye help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 12:39:25

[code]select
case when patindex('%MAH%',string)> 0 then
reverse(substring(reverse(string),patindex('%HAM%',reverse(string)),charindex(' ',reverse(string),patindex('%HAM%',reverse(string)))-patindex('%HAM%',reverse(string))))
else null
end newstring
from @t
[/code]

a.ashabi
Posting Yak Master

117 Posts

Posted - 2010-01-14 : 14:11:39

It worked
thank u so much :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources