Data Manipulation

Question

Data Manipulation

Arif Hafeez-Imam

SSChasing Mays

Points: 631
More actions
November 26, 2008 at 3:55 am

#196225

Hi,
Does anyone know how I can pull just the numeric value from a varchar field which contains both numeric and characters?
eg.
if the data stored was:
"40 Words"
or if the data stored was:
"Words 40"
all I would need is:
"40"

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

krayknot SSCertifiable Points: 7998 More actions · Answer 1

Arif Hafeez-Imam (11/26/2008)
Hi,
Does anyone know how I can pull just the numeric value from a varchar field which contains both numeric and characters?
eg.
if the data stored was:
"40 Words"
or if the data stored was:
"Words 40"
all I would need is:
"40"

hope this will work

declare @word varchar(50)

declare @count int

declare @number varchar(20)

set @count = 0

set @word = 'Words 40tytyty'

set @number = ''

while @count <= len(@word)

begin

if isnumeric(substring(@word, @count,1)) = 1

begin

set @number = @number +substring(@word, @count,1)

end

Set @count = @count + 1

end

print (@number)

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

check this great article

http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/

"Keep Trying"

rosh-396756 SSCrazy Points: 2338 More actions · Answer 3

what if it was "40 Words 60" , what should the output be?

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

I would suggest using a tally table rather than a loop 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Arif Hafeez-Imam SSChasing Mays Points: 631 More actions · Answer 5

rosh (11/26/2008)
what if it was "40 Words 60" , what should the output be?

the data that is being stored is actually house numbers with street names so it could be in the format of:

5 Jones Street

in which case i would like the 5

however if it was

Flat 4, 5 Jones Street

then I would require the 4

hope this helps.... and hope this is possible

cheers for you help so far everyone

karthik M One Orange Chip Points: 29139 More actions · Answer 6

Try this one.

Declare @String varchar(1000)

select @String = '40 ORDER'

SELECT

LTRIM(CASE LEFT(RIGHT(' '+@String,N),1)

WHEN '-' THEN '-' ELSE '' END+

SUBSTRING( RIGHT(@String,N-1),1,

PATINDEX('%[^0-9.]%',

RIGHT(' '+@String,N-1)+' ')-1))

FROM Tally

WHERE N <= LEN(REPLACE(@String,' ','!'))+1

AND PATINDEX('%[^0-9.][0-9]%',RIGHT(' '+@String,N))=1

ORDER BY N DESC

Reference:-

http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 7

If you want to know more about 'TALLY' table, i know your answer would be 'YES'. so read the below article....

http://www.sqlservercentral.com/articles/TSQL/62867/

karthik

Sergiy SSC Guru Points: 110208 More actions · Answer 8

I'm sitting next to the house - 53-57 Customs Street. "53-57" is the number.

I live on the street wherte there are houses with numbers 25, 25A, 34, 34A, 34B.

I believe if you search your table for such numbers you'll find them as well.

Are you happy to have 3 addresses with house number "34" and street names started with "A", "B", etc?

_____________
Code for TallyGenerator

rosh-396756 SSCrazy Points: 2338 More actions · Answer 9

Hi,
Does anyone know how I can pull just the numeric value from a varchar field which contains both numeric and characters?
eg.
if the data stored was:
"40 Words"
or if the data stored was:
"Words 40"
all I would need is:
"40"

:unsure::ermm:

Have u got ur answer?

:unsure::ermm:

Is the requirement clear?

:unsure::ermm:

Arif Hafeez-Imam SSChasing Mays Points: 631 More actions · Answer 10

hi guys,

sorry for delayed reply only just got round to tested and implementing it, yes it worked thanks really appreciate it cheers.

I used the PATINDEX way, seemed a little easier.

Thanks again