Capturing Values From Executing A String

Jun 14, 1999

I currently have a need to dynamically build an sql statement that always returns a single value when executed. The sql statement is always the same except the database name reference in the statment.

What I need is to be able to capture that value for later use in the procedure. Since this is a stored procedure, I can't use the "USE" statment to switch databases and I haven't been able to figure this out using the Execute statement. I can execute the string, but I can't capture the value.

I'm simply trying to execute the same set of sql statements in a stored procedure without hardcoding database names or build an identical stored procedure in all our databases.

Any help is appreciated.

View 2 Replies


ADVERTISEMENT

Capturing ID When Executing An INSERT

Jan 2, 2008

I have a bit of code that executes an INSERT statement to add a record to an existing table. This table of course has an automatically incrementing ID field, and I'd like to somehow have my INSERT statement return the value of that ID field so that I can automatically show the user the record they've added.

Is there a clean way to do this?

View 4 Replies View Related

Executing An String On MS SQL From .Net

Jan 26, 2006

Hi there,

I have this:

Dim strSQL As String = "SELECT * FROM GrupoRegistro WHERE Fecha BETWEEN '" & fini & "' AND '" & ffin & "' AND (g.IDTipoCliente = 5) OR g.Fecha BETWEEN '" & fini & "' AND '" & ffin & "' AND (g.IDTipoCliente = 3)"

How do I execute on .Net? I am using VWD2005 Express Edition and MS SQL 2000 server on a WinXp pro with MS Framework 2.0

I am using o trying and some variants :
SelectCommand="@strsqlvar">
<SelectParameters>
<asp:QueryStringParameter Name="strsqlvar" DefaultValue="strsql.string" />
</SelectParameters>


And it doesn´t work... any help will e well apreciated. thank u in advance.

View 1 Replies View Related

Transact SQL :: Convert Comma Separated String Values Into Integer Values

Jul 28, 2015

I have a string variable

string str1="1,2,3,4,5";

I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :

declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)

But this query throws following error message:

Conversion failed when converting the varchar value '3,4,6,7' to data type int.

View 4 Replies View Related

SQL Server 2012 :: Replace All Values In String With Values From Look Up Table

Mar 19, 2014

I have a table that lists math Calculations with "User Friendly Names" that look like the following:

([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])

I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)

The new formulas need to look like the following:

([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])

I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.

How can I accomplish this?

Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.

DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )

[Code] .....

View 3 Replies View Related

Integration Services :: SSIS Reads Nvarchar Values As Null When Excel Column Includes Decimal And String Values

Dec 9, 2013

I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?

CREATE TABLE [dbo].[Import_CustomerSales](
 [CustomerId] [nvarchar](50) NULL,
 [CustomeName] [nvarchar](50) NULL,
 [CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]

View 5 Replies View Related

Error While Executing A A Query String Using EXEC Statement

Sep 18, 2007

Hi,
I have written a stored proc to bulk insert the data from a data file.
I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc

The follwing statement works fine if i give the table name directly in the query



Code Snippet

DECLARE @LastUpdate varchar(20)

SET @LastUpdate = 'Dec 11 2007 1:20AM'

INSERT INTO Category
SELECT MSISDN, @LastUpdate FROM OPENROWSET( BULK '\remotemachinedatafile.txt',
FORMATFILE = '\remotemachineFormatFile.fmt',
FIRSTROW = 2) AS a



To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below






Code Snippet

@Category - Will be passed as a parameter to the stored proc


DECLARE @vsBulkSQL VARCHAR(MAX)

DECLARE @LastUpdate varchar(20)

SET @LastUpdate = 'Dec 11 2007 1:20AM'

SELECT @vsBulkSQL ='INSERT INTO '+ @Category + ' SELECT MSISDN, ''' + @LastUpdate +''' FROM OPENROWSET ' + '( BULK ' + '''' + '\remotemachinedatafile.txt'+ ''''+ ' ,' +
+ ' FORMATFILE ' + '=' + ''''+ '\remotemachineFormatFile.fmt'+ ''''+ ',' +
' FIRSTROW ' + '=' + '2' + ')' + ' AS a'

Print @vsBulkSQL - This prints the folliwing statement


INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineDataFile.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a


Exec @vsBulkSQL - This statement gives the following error

The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineSecond.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.






Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same

~Mohan

View 4 Replies View Related

Error Executing A Sql Statement Against A Table With Null Date Values

Apr 10, 2008

We are just upgrading a server from 2000 to 2005 and we are getting the message below when we execute a sql statement against a table with a date field with null values:


"Error converting data type DBTYPE_DBTIMESTAMP to datetime."

View 3 Replies View Related

How To Use String Values? I Really Need Help For This. Thanks.

Dec 10, 2004

Hello,

I am working with ASP.NET/VB and Microsoft SQL 2000 database.

I have a search form where keywords are submitted.
Consider I write the the keywords 'asp' and 'book'. The results page is called as follows: results.aspx?search=asp%20book

Then I use this script in results.aspx to put the keywords in a string:

Sub Page_Load(sender As Object, e As System.EventArgs)
Dim keywords() As String = Request.QueryString("search").Split(CChar(""))
End Sub

My table is set for FULL TEXT SEARCH.
Consider the SQL when I look for records containing 'asp' and 'book':

SELECT *
FROM dbo.documents
WHERE CONTAINS (*, 'ASP or BOOK')

This SQL looks only for these words. What I need is to look for records that contain the Keywords included in the string keywords().

Can you tell me how to access the string values in the SQL and use it?

Thanks,
Miguel

View 2 Replies View Related

Averaging String Values?

Jul 6, 2006

SQL neophyte hoping this is an easy answer...

I'm trying to obtain an average for a set of string values. "Huh?" (In an investment portfolio, some securities have ratings, e.g., AAA or Aa2 or BBB, etc., and to obtain an overall rating for the portfolio you have to find a way to average the string values from each individual security.) I figured I'd assign a numerical value to each, average, and convert back to string. My first attempts have been a little futile...can anyone help?

View 10 Replies View Related

Order By With String Values

May 27, 2008

Hi all,
I have table with id column of nvarchar..

Now,WHEN I RUN THIS QUERY,
select ID from <TABLE>,
IT RETURNS

erfq-1
erfq-10
erfq-100
erfq-1000
erfq-10000
erfq-1001
erfq-101
erfq-11
erfq-12
erfq-19
erfq-2
erfq-3
erfq-30
erfq-4
erfq-5
erfq-50
erfq-6
erfq-9
erfq-99
erfq-999

WHERE AS I NEED IT AS BELOW

erfq-1
erfq-2
erfq-3
erfq-4
erfq-5
erfq-6
erfq-9
erfq-10
erfq-11
erfq-12
erfq-19
erfq-30
erfq-50
erfq-99
erfq-100
erfq-101
erfq-999
erfq-1000
erfq-1001
erfq-10000


How Can I achive this one ?

Thanks in Advance
Dana

View 7 Replies View Related

Format Values Into One String

Oct 18, 2007

HI!

I do a select and concatinating the answers into a one column table @tab(string).
Is it possible to format these values like this:
GENAV DELAV TOTIP RES
12 myvalues www.notech.com 1
1 starthere 192.168.0.2 1
125 or here www.hereistomanychar.s 0

max 3 max 10 max 22 max 1

the first column max 3 characters the second column max 10 char...
I want to do this to get a good view of the data i the table @tab

Here is the select:


INSERT INTO @tab

SELECT Convert( varchar, GENAV)+ ' ' + Convert( varchar, DELAV)+ ' ' + Convert( varchar, TOTIP) ' ' + Convert( varchar, RES)

from dbo.MOVE

where DATE between @starttime and @theEndTime


With this select it looks like:

12 myvalues www.notech.com 1
1 starthere 192.168.0.2 1
125 or here www.hereistomanychar.s 0

This is very complicated to read!

View 6 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu

Jan 24, 2008



Hi,

I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window

Please see the image through this url http://kyxao.net/127/ExecutionProblem.png


Any ideas for this issue?

Thanks a lot

View 1 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window

Jan 23, 2008

Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack 

View 2 Replies View Related

Different Results When Executing From .NET Component Compare To Executing From SQL Management Studio

Oct 10, 2006

Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys

View 2 Replies View Related

Conditionally Grouping Different String Values Together

Jan 29, 2008

 I have a sql server table column that can contain the following possible values. 1. 766/IT 2. 777/HR3.  890/EG4.  012/AS5.  Trainee6.  Contractor 7.  Others I want to write an SQL query grouped by this column, Trainee should be one group, Contractor should be another different group, Others should be another different group and then  every thing else ( 766/IT ,777/HR ,890/EG, 012/AS) should be grouped together as one group. Think of it in terms of a pie chart with those groups. 

View 4 Replies View Related

Possible To Return A String Of Table Values?

Dec 30, 2003

This is a portion of my stored procedure in T-SQL

CREATE PROCEDURE GetPoll
(
@PollID INT,
@PollQuestion VARCHAR(1000) Output,
@PollOptions VARCHAR(1000) Output,
@PollPercentages DECIMAL Output
)

AS

SELECT @PollQuestion = (SELECT PollQuestion FROM Polls WHERE PollID = @PollID)

SELECT @PollOptions = (SELECT [Option] FROM PollOPtions WHERE PollID = @PollID)


*SELECT @PollPercentages = (SELECT [Option], COUNT([Option]) As Num_Votes FROM Votes WHERE PollID = 1 GROUP BY [Option])


The final part(*) of this SQL will return more than one value, so is it possible for me to return all that information in one varaible with one SELECT query?? Or would I need to use a loop and query a value at a time, and store it into a delimited string?

thx in advance...

View 3 Replies View Related

Rowset Values In A Single String

Apr 8, 2004

I am not close to an sql server today and this question was posed to me. can someone hook me up?

" I want to query a column of values and place them into a single string seperated by commas" (as a function)

Table a
123
456
789
321
654
987


output
123,456,789,321,654,987

thanks in advance

View 14 Replies View Related

NULL Values Vs Empty String Vs Space

Oct 25, 2006

How do I define a field to have the default value = ''. Not NULL but not a space either in SQL Server 2005?

View 10 Replies View Related

T-SQL (SS2K8) :: How To Cut Certain Values In A String To Separate Columns

Jun 5, 2014

I have a column containing values for different languages. I want to cut out the values per languate in a seperat column.

The syntax is a 2 letter country code followed by : the value is contained in double quotes. each languate is separated by a ; (except for the last one)

EX ur English, Dutch and Swedish:US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning"

The result would Be
column header US
with value Project/Prescription sale

next column header NL
with value Project/specificatie etc.

Here are table examples:

IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL
DROP TABLE [#SALETYPE]

CREATE TABLE [#SALETYPE](
[SaleType_Id] [int] NOT NULL,
[name] [nvarchar](239) NOT NULL,

[Code] ....

View 9 Replies View Related

SQL Server 2008 :: Get Numeric Values From String

Jul 29, 2015

Why can i get the numeric values from this string?

{_cpn}=1743; {_cpnll}=4511

Result: 1743, 4511

Position and len of the value can be different...

View 6 Replies View Related

Find Values From String In A Single Cell

Jan 10, 2014

I have a program which feeds back information to a table within my database. The annoying thing is that only 1 of the table cells has all of the information I need so essentially I have a large string of data I need to retrieve values from.I retrieve my cell with a simple select details from alerts where ID = (alert number).The results I have are as follows:

[INFO] Dashboard Output Information:
[LastUpdated]
Time=09:59
[LastProcessed]
LastProcessed=*2911
Updated=09:59
[LastReceived]
LastReceived=#10
Updated=09:59

As you can see I have a list (its larger but this is a sample), the only thing I need are the values for each section so for last processed i want the 2911 so I can use it on my dashboard and the last received I want the 10 for that section.i have read about using substrings but am really not sure where to start and how I'd go about getting these values to then use elsewhere?

View 3 Replies View Related

Problem Concatenating Column Values Into String...

Apr 5, 2006

I have a customer who has recently migrated their SQL server to a newserver. In doing so, a portion of a stored procedure has stoppedworking. The code snippet is below:declare @Prefixes varchar(8000),declare @StationID int-- ...select @Prefixes = ''select @Prefixes = @Prefixes + Prefix + '|||'from Devicewhere Station_ID = @StationIDEssentially, we are trying to triple-pipe delimit all the deviceprefixes located at a specified station. This code has workedflawlessly for the last 10 months, but when the database was restoredon the new server, @Prefixes only contains the prefix for the lastdevice.Is there a server, database, or connection option that permits this towork that I am not aware of? Why would this work on the old server andnot on the new? (BTW - both servers are running SQL 2000 StandardSP4).Thanks!

View 6 Replies View Related

Reporting Services :: SSRS - How To Sum String Values

Nov 24, 2015

The data source is Fetch XML - so all the values are string.I need to sum the values but getting an error:

Warning  1      [rsAggregateOfNonNumericData] The Value expression for the textrun ‘Textbox25.Paragraphs[0].TextRuns[0]’ uses a numeric aggregate function on data that is not numeric.  Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.I tried converting it to INT but it returns a large number which does not match what I'm expecting.

View 10 Replies View Related

Filters &&amp; Expression With String Values, Should They Work??

Dec 7, 2007

Any idea why this expression would not work in SSRS?

Based on a expression on a group textbox I get 0 records that match:
=Iif(Fields!ACCRUAL_CODE.Value <> "A", CountDistinct(Fields!LOAN_NBR.Value), 0)



I've evaluated in the proc & there should be a count of 29 records.

I found a work around by using this in the stored proc & I get my record count:


ACCRUAL_CODE = CASE WHEN BASE.ACCRUAL_CODE = 'A' THEN 0 ELSE 1 END

and changed the expression in SSRS to this & it works:
=sum(Fields!ACCRUAL_CODE.Value)



Is this a known issue with strings? From searching on this site I see that string evaluations are widely used so I do not see why it wouldn't work. I've also see this when filtering the dataset on anything that is a character. Any suggestions?

View 3 Replies View Related

Displaying Multiple Values In A Concatenated String

Oct 17, 2007

SQL Server 2005.(SP2). MS SSRS;
I want to display some numbers in the same line as a concatenated string. For example a Customer may have multiple bills. These bill numbers are displayed in separate rows. I want to display them all on the same line.
Example of current display:
Customer Bill #
ABC Company 123
ABC Company 456
ABC Company 789 etc

I want this to display as below:
Cusotmer Bill #
ABC Company 123, 456, 789, etc.

Is this possible in SSRS. Please help me with the syntax.

Thanks in advance.

View 2 Replies View Related

Concatinating String Values From Multiple Rows

Nov 4, 2006

I currently have some SQL code that is used to build a string that is a concatination of string values across multiple rows.  The subqueries in the script sometimes return NULL values so I use the following statement to change the default behavior of the concatination operator which prevents my query from returning NULL:

SET CONCAT_NULL_YIELDS_NULL ON

Here's the code snippet:

select DISTINCT

(SELECT CASE WHEN (t1.MaskValue & HDR.TranTypeID)=1 THEN ' ' + t1.description ELSE '' END FROM transactiontypes t1 WHERE (t1.MaskValue & HDR.TranTypeID)=1) +

(SELECT CASE WHEN (t2.MaskValue & HDR.TranTypeID)=2 THEN ' ' + t2.description ELSE '' END FROM transactiontypes t2 WHERE (t2.MaskValue & HDR.TranTypeID)=2) +

(SELECT CASE WHEN (t3.MaskValue & HDR.TranTypeID)=4 THEN ' ' + t3.description ELSE '' END FROM transactiontypes t3 WHERE (t3.MaskValue & HDR.TranTypeID)=4) +

(SELECT CASE WHEN (t4.MaskValue & HDR.TranTypeID)=8 THEN ' ' + t4.description ELSE '' END FROM transactiontypes t4 WHERE (t4.MaskValue & HDR.TranTypeID)=8) +

(SELECT CASE WHEN (t5.MaskValue & HDR.TranTypeID)=16 THEN ' ' + t5.description ELSE '' END FROM transactiontypes t5 WHERE (t5.MaskValue & HDR.TranTypeID)=16)) as 'Transaction Type'

FROM HDResponse HDR

Here's the underlying table structure:
CREATE TABLE [dbo].[TransactionTypes](
             [ID] [int] IDENTITY(1,1) NOT NULL,
             [Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,        [MaskValue] [int] NOT NULL) ON [PRIMARY]
CREATE TABLE [dbo].[HDResponse](
             [ResponseID] [int] IDENTITY(1,1) NOT NULL,
             [LoggedDateTime] [datetime] NULL,
             [ResponseTypeTripID] [int] NULL,
             [ResponseTypeID] [int] NULL,
             [ResponseTypeObjectID] [int] NULL,
             [ObjectID] [int] NULL,
             [IDHolderID] [int] NULL,
             [TransportCode] [int] NULL,
             [CardID] [int] NULL,
             [IssueCode] [smallint] NULL,
             [EventDateTime] [datetime] NULL,
             [Response] [bit] NULL,
             [TranTypeID] [int] NULL)
             ON [PRIMARY]

The problem I am having is I need to be able to use the query above in a view used for reporting.  Unfortunately, you cannot use SET CONCAT_NULL_YIELDS_NULL ON in a view.  This causes my query to return NULL if any of the subqueries return NULL.  I could create a function to do something similar and reference the function in the query but I can't help but think there must be a way to get this done in a single query.

Any thoughts or ideas would be greatly appreciated.

Thanks!!!!!

View 1 Replies View Related

Converting String Parameter Values To Date

Mar 11, 2008



Hi,

I have a calendar parameter in one of my reports. The values of the calendar are of the form 'yyyy-mm-dd hh:mms'. And they are string values.

Now when I generate the report I have a textbox that takes the parameter label (e.g., parameter!calendar.label). However, I would like to format (or trim) the label as just 'yyyy-mm-dd'. Does anyone know how I can do this? I tried to change the parameter value format to datetime but this just throws an invalid datatype error...

cheers,
Al

View 10 Replies View Related

'combining' Values From A Select Statement Into A String

Jan 10, 2008

Hi,

I got a really simple question here. Say I have a table with

ID, Name
1, A1
2, A2
3, A3
....
10, A10

Now I want to combine the names into another table grouped by their ID (say 1-5, 6-10), so this new table has two names instead of 10:
A1 A2 A3 A4 A5
A6 A7 A8 A9 A10

Is there a function that allows me to 'combine' the names from a select statement?

Thanks in advance,
Steven

View 6 Replies View Related

INSERT INTO Command (two Columns) With One Fixed Value, One String With Several Values

Nov 7, 2006

I have a string with values value1,value2, value3, value(n) which I would like to append to my table.
In the second column of my table I have a parameter which stays the same, so I end up with (if the parameter value is "123456")
Column 1   |         Column 2
123456      |         Value1
123456      |         Value2
123456      |         Value3
I would like to set up a single SQL statement which will process this regardless of the number of values (therefore rows) desired.
Something like:
INSERT INTO dbo_tblUserLevelApplicationRequests ( Column1, Column2) select EmployeeNumberParam as EmployeeNumber, ((stringofvalues) as valuestring)
Is it possible to do this with a single SQL statement?

View 2 Replies View Related

Concatenated String Of Comma Separated Values (was Help With Query)

Nov 21, 2006

I have following 2 queries which return different results.


declare @accountIdListTemp varchar(max)
SELECT COALESCE(@accountIdListTemp + ',','') + CONVERT(VARCHAR(10),acct_id)
FROM (SELECT Distinct acct_id
FROM SomeTable) Result
print @accountIdListTemp



The above query return the values without concatenating it.


declare @pot_commaSeperatedList varchar(max)

SELECT DISTINCT acct_id
into #accountIdListTemp
FROM SomeTable


SELECT @pot_commaSeperatedList = COALESCE(@pot_commaSeperatedList + ',','') + CONVERT(VARCHAR(100),acct_id)
FROM #accountIdListTemp
print @pot_commaSeperatedList
drop table #accountIdListTemp



This query returns result as concatenated string of comma separated values.

If i want to get similar result in a single query how can i get it?

View 4 Replies View Related

Using Stuff To Return A String Of Values From A Column Without A Cursor

Dec 13, 2007

I think it was Pat Phelan who posted a little trick here where he used the STUFF function to create a string fo values from a column without using a cursor.

I am starting a brand new project and I did my table design and I am awaiting a finalized requirements document to start coding and I thought I would spend a little time writing some code to autogenerate some generic one record at a time SELECT, INSERT,UPDATE and DELETE stored procedures. With the coming holiday things are getting quiet around here.

The code that is not working is listed below. It does not work. It returns Null. I suck.

DECLARE @column_names varchar(8000)

SET @column_names = ''

SELECT @column_names = STUFF(@column_names,LEN(@column_names),0,C.COLUMN_ NAME + ', ')
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = 'MyTable'

SELECT @column_names

little help?

View 6 Replies View Related

Single String Replace Logic - 0 Needs To Be Ignored For Decimal Values

May 10, 2012

The string column value looks like as below. Each value has a size of 15 withing a string

'2.2020 30 4.0000'

The column value should match with user input as below. The result should show equal when it is compared. Currently, it results not equal since it is a string comparision. The last digit '0' needs to be ignored for decimal values.

'2.202 30 4.0'

I need to handle the decimal values in such a way, if staring value with '.' and last digit is 0 then replace with space ''. So, it should look like

'2 2 2 30 4 ' = '2 2 2 30 4 '

When this string is compared, it results in EQUAL.

I tried the below logic, which even replaces the integer value like 30 to 3 and 3000 to 3 and results in equal which is incorrect.

RTRIM(REPLACE(REPLACE(RT1.rate,'''+@DOT+''','''+@S PACE+'''), '''+@ZERO+''', '''+@SPACE+''')) = '''+REPLACE(REPLACE(@Rate,'.',' '), '0', ' ')+''' '
Ex:'2.2020 300 4.00' = '2.20200 30 4.0'

After replace, string looks like

Ex:'2 2 2 3 4 ' = '2 2 2 3 4 '

It results as EQUAL which is incorrect. I need only decimal value to be replaced not integer.

I am looking for a single string replace logic.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved