searching string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

searching string

Author

Topic

sql_basic
Starting Member

9 Posts

Posted - 2012-04-05 : 12:40:44

Hi All,

How do i search a decimal number in a string.

For e.g string is @csv_string = 1070-1044-CPFEE7-2393585 30.716859

I need to search a decimal number which is 30.716859 for this case. Also the decimal mumber will not remain constant.

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-05 : 13:16:29

will it's place in the string be constant..like at the end?

Do it in pieces..ask yourself, step by step, what do I need to do...starting with the first step


DECLARE @csv_string varchar(max) = 'IMR1070CPFSY005Mar12][1070-1044-CPFEE7-2393585 MISCELLANEOUS USD User 30.716859'

SELECT REVERSE(@csv_string)
, CHARINDEX(' ',REVERSE(@csv_string))
,SUBSTRING(REVERSE(@csv_string),1,CHARINDEX(' ',REVERSE(@csv_string)))
,REVERSE(SUBSTRING(REVERSE(@csv_string),1,CHARINDEX(' ',REVERSE(@csv_string))))
,CONVERT(decimal(30,15),REVERSE(SUBSTRING(REVERSE(@csv_string),1,CHARINDEX(' ',REVERSE(@csv_string)))))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 13:19:48

will number be always the last part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql_basic
Starting Member

9 Posts

Posted - 2012-04-05 : 14:20:06

quote:
Originally posted by visakh16

will number be always the last part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes the decimal number will always be last

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 16:22:13

you can do it with STUFF function also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-05 : 16:55:13

Here's a variation on Brett's solution that will work in case the number is not preceded by a space:

SELECT REVERSE(LEFT(REVERSE(@csv_string),PATINDEX('%[^0-9.-]%',REVERSE(@csv_string))-1))

This returns a string, you can add the CONVERT function if needed.

sql_basic
Starting Member

9 Posts

Posted - 2012-04-06 : 11:48:24

quote:
Originally posted by X002548

will it's place in the string be constant..like at the end?

Do it in pieces..ask yourself, step by step, what do I need to do...starting with the first step

DECLARE @csv_string varchar(max) = 'IMR1070CPFSY005Mar12][1070-1044-CPFEE7-2393585 MISCELLANEOUS USD User 30.716859'

SELECT REVERSE(@csv_string)
, CHARINDEX(' ',REVERSE(@csv_string))
,SUBSTRING(REVERSE(@csv_string),1,CHARINDEX(' ',REVERSE(@csv_string)))
,REVERSE(SUBSTRING(REVERSE(@csv_string),1,CHARINDEX(' ',REVERSE(@csv_string))))
,CONVERT(decimal(30,15),REVERSE(SUBSTRING(REVERSE(@csv_string),1,CHARINDEX(' ',REVERSE(@csv_string)))))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Thanks i got it. How do i approach if i want to check whether the number in string is a decimal number or not.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 12:11:14

use logic like

IF number = FLOOR(Number) then 'int' else 'decimal' end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql_basic
Starting Member

9 Posts

Posted - 2012-04-06 : 12:36:30

quote:
Originally posted by visakh16

use logic like

IF number = FLOOR(Number) then 'int' else 'decimal' end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 15:23:06

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql_basic
Starting Member

9 Posts

Posted - 2012-04-09 : 11:06:36

A bit of modification to above query

select @csv_string='abc,1223.00,N,26594.875901,'

Yes there is a comma after last decimal number. Also the number will not be same but its position will remain constant.
Now i want to get to the last number in above string and i want to replace the decimal number with 9999.875901 if the number of digits on the left side of the decimal exceeds length 4.

I tried below lines but struck after this

SELECT REVERSE(@csv_string)
,PATINDEX('%[,]%',REVERSE(@csv_string))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-09 : 11:31:14

so is it always 9999.875901 that you want number to be replaced with?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql_basic
Starting Member

9 Posts

Posted - 2012-04-09 : 12:26:07

quote:
Originally posted by visakh16

so is it always 9999.875901 that you want number to be replaced with?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes the value need to be changed to 9999 if the number of places to the left of decimal exceeds 4.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-04-09 : 13:49:28

This is mighty ugly

declare @csv_string varchar(50)='abc,1223.00,N,26594.875901,'

SELECT
REVERSE(
CASE WHEN CHARINDEX(',',substring(REVERSE(@csv_string),2,50))
- CHARINDEX('.',REVERSE(@csv_string)) > 4
THEN SUBSTRING(REVERSE(@csv_string),1,CHARINDEX('.',REVERSE(@csv_string)))
+ '9999'
+SUBSTRING(REVERSE(@csv_string),CHARINDEX(',',substring(REVERSE(@csv_string),2,50))+1,50)
ELSE NULL
END
) as NewString
,@csv_string

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources