substring
-
Hi all,
I have a varchar that I need to extract a number out of.
Example:
select '13 & 9'
What can I do to that to get only the first number out? The following would be good but I'm not that lucky:
select replace('13 & 9', ' &%', '')
-
I'd use SUBSTRING with CHARINDEX - Is a space char always what you are going to look for?
-
Yep, the format will always be:
number1 space ampersand space number1
I want number1 only.
-
Whoops, forgot to mention that sometimes the filed may not have 2 numbers.
So sometimes it's like: '19 & 133'
And other times is's just: '19'
I need to make it work for both cases.
-
Lol... excellent
-
Got it, thanks to the suggestion of using charindex and substring. Oh and also a CASE.
-
Assuming ColumnName is the name of your column (and Col1 & Col2 are additional fields)...
Select Col1, Col2, Case
When CharIndex(' ', ColumnName) = 1 Then Substring(ColumnName, 1, CharIndex(' ', ColumnName))
As FirstNumber
From TableName
... I think that will work... though you may need to subtract 1 from the index of the space (I can't remember if CharIndex is a 1-based or zero-based function).
-
Yep, almost identical. Thanks.
-
Don't need Case if there's always a space (ack...
I'm a poet and don't know it) 
DECLARE @Col VARCHAR(20)
SET @Col = '13 & 9'
SELECT LEFT(@Col,CHARINDEX(' ',@Col+' ')-1)
SET @Col = '13'
SELECT LEFT(@Col,CHARINDEX(' ',@Col+' ')-1)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
SELECT CASE WHEN ISNUMERIC() = 1 THEN
ELSE LEFT(, PATINDEX('%^[0-9]%', )-1)
END
FROM
-
Sorry about that, this treats less-than / greater-than symbols as HTML. I'll try that again:
SELECT CASE WHEN ISNUMERIC(Colname) = 1 THEN Colname
WHEN PATINDEX('%^[0-9]%', ColName) > 0
THEN LEFT(ColName, PATINDEX('%%', ColName) -1)
END
FROM TableName
-
Uh... Don't know about anyone else, but I wouldn't use ISNUMERIC for this... case in point...
SELECT ISNUMERIC('13E5')
SELECT ISNUMERIC('13D5')
SELECT ISNUMERIC('13.5')
SELECT ISNUMERIC('13,5')
SELECT ISNUMERIC('$135')
etc... etc...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
No kidding!! I can see 1, 3, 4, and 5....
What is '13D5'? Why does it evaluate to 1300000?
-
Scientific and Engineering notations...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply