[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 693
  • Last Modified:

SQL extract number of a string

Hello

I use this function to exctract number out of a string, it worked great until somebody put the
word 'Inc.'  instead of a number the '.' created a problem and generate an error:

declare @param varchar(50)
select Left(SubString(@param, PatIndex('%[0-9.-]%', @param), 8000), PatIndex('%[^0-9.-]%', SubString(@param, PatIndex('%[0-9.-]%', @param), 8000) + 'X')-1)

Thanks for the help
0
arnololo123
Asked:
arnololo123
1 Solution
 
LordKnightshadeCommented:
If you only need to worry about accounting for the period just wrap the SELECT value in a REPLACE targeting the period.  It's not particularly elegant, but it works :)

declare @param varchar(50)
select @param = 'Inc.'
select REPLACE(Left(SubString(@param, PatIndex('%[0-9.-]%', @param), 8000),PatIndex('%[^0-9.-]%',
	SubString(@param, PatIndex('%[0-9.-]%', @param), 8000) + 'X')-1),'.','')

Open in new window

0
 
arnololo123Author Commented:
Well the problem with this approach is that the value 3.25 for example would be stripped of the period.
0
 
LordKnightshadeCommented:
Is it feasible for you to use a second variable in the function?  If so you can cut down on the code by evaluating a variable after the evaluation.

declare @param varchar(50), @param2 varchar(50)
select @param = '3.25'
select @param2 = Left(SubString(@param, PatIndex('%[0-9.-]%', @param), 8000),PatIndex('%[^0-9.-]%',
	SubString(@param, PatIndex('%[0-9.-]%', @param), 8000) + 'X')-1)
select (CASE @param2 WHEN '.' THEN '' ELSE @param2 END)

Open in new window

0
 
Scott PletcherSenior DBACommented:
declare @param varchar(50)
select Left(SubString(@param, PatIndex('%[^a-z][0-9.-]%', @param), 8000), PatIndex('%[^0-9.-]%', SubString(@param, PatIndex('%[^a-z][0-9.-]%', @param), 8000) + 'X')-1)
0
 
mimran18Commented:
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now