| Author |
Topic |
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
 Posted - 2008-08-15 : 13:05:23
|
| Hi All,I have a table called Table1 with a field called Packsize [nvarchar(50)]. The records are like this.Packsize10.5 OZ12 OZ .250 OZ 5.2 OZ 22.5 OZ I just need a select query that can return the numeric part only without any spaces. The Select query in above example should return the following.Packsize10.512 .2505.2 22.5 How to do this?Thanks for you prompt help.Zee |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-15 : 13:09:47
|
| SELECT LTRIM(RTRIM(REPLACE(Packsize,'OZ',''))) FROm YourTable |
 |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2008-08-15 : 13:35:04
|
| Visakh16,It wont be OZ all the time, it can be any thing FZ,OZZ,MD, etc.Please help.Thanks,Zee |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2008-08-15 : 13:57:24
|
| tkizer thanks for your help.But your script returns some results inclduing string. For example the following is returning as it is:12-5.5OZ4-4.14-5.75OZ4-4.124-5.5OZThe following script runs well and have return no string so far.Select rtrim(ltrim(left(packsize,len(packsize)-2))) from product |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-15 : 13:57:38
|
| or use LEFT()LEFT(Packsize,CASE WHEN CHARINDEX(' ',Packsize)>0 THEN CHARINDEX(' ',Packsize)-1 ELSE LEN(Packsize) END) |
|
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2008-08-15 : 14:01:49
|
| Now what if I have to do the opposite & just want to return the string and not the numeric part?For example 4-5.75OZ should be return as OZ, and 23.5 ZZ should return ZZHow can I do this.Thanks for the help. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-15 : 15:06:55
|
SELECT LEFT(Col1, COALESCE(NULLIF(PATINDEX('%[^0123456789-.]%', Col1) - 1, -1), LEN(Col1))FROM Table1
E 12°55'05.25"N 56°04'39.16" |
|
|
|
BalochDude
Starting Member
1 Post |
Posted - 2009-08-02 : 08:20:44
|
| I had a same problem and I was searching for the solution. I think I have found solution to my problem. May be it will also help you.[url]http://www.kf7.co.uk/sql-server-function-get-numeric.aspx[/url]The solution on this page looks more like a sql server 2000 script. I guessed it from "varchar(7999)". I have changed it to "text" in my function.Anyways, happy coding guys. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-02 : 08:46:32
|
VARCHAR(MAX) would be a better substitute.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
TranceVibes
Starting Member
1 Post |
Posted - 2010-07-13 : 05:20:17
|
| If I were u, I would use the REGEXP_SUBSTR function if available on your database...http://psoug.org/reference/regexp.html |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-13 : 05:35:07
|
quote:
Originally posted by TranceVibes
If I were u, I would use the REGEXP_SUBSTR function if available on your database...http://psoug.org/reference/regexp.html
No it isn't available in SQL Server.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
How to get numeric part of the string?