-1

I want to delete the characters after numbers in one of my columns:

Column values are like this :

2GB 
3G
28GB
7G
90G

as you can see there is no pattern in these numbers except I have a one or two digits number and letter G or GB after them.

What query can detect the numbers and delete the characters after that?

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rman Edv
  • 163
  • 1
  • 17
  • 1
    Please make an attempt. – Mitch Wheat Aug 09 '14 at 09:19
  • I did actually, before i post my question here i googled it, http://social.technet.microsoft.com/wiki/contents/articles/17948.t-sql-right-left-substring-and-charindex-functions.aspx – Rman Edv Aug 09 '14 at 09:25
  • but still i couldn't figure out a way to detect characters and separate numbers from them. – Rman Edv Aug 09 '14 at 09:26
  • possible duplicate of [T-SQL select query to remove non-numeric characters](http://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters) – Tanner Aug 09 '14 at 09:33

1 Answers1

2

Try this..

drop table #t
create table #t(id varchar(10))
insert into #t values('2GB'), 
            ('3G'),
            ('28GB'),
            ('7G'),
            ('90G')


            update #t
            set id=substring(id,0,PATINDEX('%[GB]%',id)) from #t
            select * from #t

See Demo

vhadalgi
  • 7,027
  • 6
  • 38
  • 67