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
ADVERTISEMENT
Nov 7, 2006
Ok.. so I have a fixed position data feed. I read the file in as just whole rows initially, process a specific position and evaluate a conditional split to determine direction of the file for proper processing (file contains multiple recors with different layouts). This all works fine. I then use the derived column feature to process all the columns.
Most of the columns are as simple as SUBSTRING(RecordData,1,10) for example to get the first column. This all works for string data. I then have a column that is a date field. The problem occurs that the code SUBSTRING(RecordData,20,10) returns either a date or empty set of data if no date was in the original file. When this gets sent to the OLEDB connection (SQL Server 2005) into the date field it fails. If the record has a date it works, but if it is empty it fails the insert.
I tried to replace empty strings with NULLs with this code. REPLACE(TRIM(SUBSTRING(RecordData,20,10)),"",NULL(DT_WSTR,10)). This does not work. So my question is how do I bring a date field from a fixed flat file into a SQL datetime field using a derived column? More specifically is how do I set it to NULL if its empty data? When I use the above code it inserts all the rows from the file, but it sets all rows to NULL not just the empty ones.
Thanks.
View 6 Replies
View Related
Jun 14, 2005
Hi.
1.
Have a query that fetches a real value (e.g. 4.3345643)
Let say the field is called TheReal.
How can I format the value in the select statement so I get a thousand separator(s) and two decimals in the resultset.
2. In the same query I have a left join as well.
Table 2 retur (sometimes) <NULL>.
Is it possible to force this <NULL> value to be a fixed string value instead in the select statement.
View 6 Replies
View Related
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
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
Aug 18, 2015
how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.
DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))
[code]....
View 7 Replies
View Related
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
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
Dec 1, 2007
i have a report with contains preview of percentage columns example of percentage of student marks in perticular subject like 95%. and if suppose any student not attend any test i have to dispaly like not attended statement.
so i have display two fields like 95% and not attended statement in same column, i given Cstr(Fields!Data.Value), it gives two fields with contains not attended statement of perticular query and it dispalys 0.95 % . but i need 95% and not attended statement for perticular query in same column.
is there any solution for dispalying string and percentage values in single column for given perticular query and those two values are disply same result compare with preview at the time of export to excel sheet
plese send solutions ASAP
Thanks
James vs
View 1 Replies
View Related
Dec 8, 2013
I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?
View 2 Replies
View Related
Jul 23, 2005
I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?
View 6 Replies
View Related
Sep 8, 2015
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
View 5 Replies
View Related
Aug 22, 2007
I have the following:
-----------------
WHILE PATINDEX('%,%',@Columns)<> 0 BEGIN
SELECT @Separator_position = PATINDEX('%,%',@Columns)
SELECT @array_Value = LEFT(@Columns, @separator_position - 1)
SET @FieldTypeID = (SELECT FieldTypeID FROM [Form].[Fields] WHERE FieldID = (CAST(@array_Value AS INT)))
SET @FieldName = (SELECT [Name] FROM [Form].[Fields] WHERE FieldID = @array_Value)
print 'arry value' + CONVERT(VarChar(500), @array_value)
print 'FieldTypeID: ' + CONVERT(VARCHAR(500), @FieldTypeID)
PRINT 'FieldName: ' + @FieldName
BEGIN
IF @FieldTypeID = 1 OR @FieldTypeID = 2 OR @FieldTypeID = 3 OR @FieldTypeID = 9 OR @FieldTypeID = 10 OR @FieldTypeID = 7
BEGIN
SET @InnerItemSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[ItemDetailFieldRecords] IDFR WHERE IDFR.ItemDetailID = ID.ItemDetailID AND IDFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' '
SET @InnerTaskSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[TaskFieldRecords] TFR WHERE TFR.TaskID = T.TaskID AND TFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' '
END
ELSE IF @FieldTypeID = 4 OR @FieldTypeID = 8 --DropDownList/RadioButtonlist
BEGIN
SET @InnerItemSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[ItemDetailFieldListRecords] IDFLR ON FFLV.FieldListValueID = IDFLR.FieldListValueID WHERE IDFLR.ItemDetailID = ID.ItemDetailID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
SET @InnerTaskSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[TaskFieldListRecords] TFLR ON FFLV.FieldListValueID = TFLR.FieldListValueID WHERE TFLR.TaskID = T.TaskID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
END
ELSE IF @FieldTypeiD = 5 --Cascading
BEGIN
SET @InnerItemSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[ItemDetailFieldCascadingRecords] IDFCR ON IDFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE IDFCR.ItemDetailID = ID.ItemDetailID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
SET @InnerTaskSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[TaskFieldCascadingRecords] TFCR ON TFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE TFCR.TaskID = T.TaskID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
END
ELSE IF @FieldTypeiD = 6 --ListBox
BEGIN
SET @InnerItemSelect = ' (SELECT i.[CSV] FROM @ItemDetailLV i WHERE i.ID = ID.ItemDetailID AND i.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
SET @InnerTaskSelect = ' (SELECT it.[CSV] FROM @TaskLV it WHERE it.ID = T.TaskID AND it.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
END
ELSE IF @FieldTypeID = 11 --Users
BEGIN
SET @InnerItemSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[ItemDetailUserRecords] IDUR ON SU.UserID = IDUR.UserID WHERE IDUR.ItemDetailID = ID.ItemDetailID AND IDUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
SET @InnerTaskSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[TaskUserRecords] TUR ON SU.UserID = TUR.UserID WHERE TUR.TaskID = T.TaskID AND TUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
END
ELSE IF @FIelDTypeID = 12 --Group
BEGIN
SET @InnerItemSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[ItemDetailGroupRecords] IDGR ON SG.GroupID = IDGR.GroupID WHERE IDGR.ItemDetailID = ID.ItemDetailID AND IDGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
SET @InnerTaskSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[TaskGroupRecords] TGR ON SG.GroupID = TGR.GroupID WHERE TGR.TaskID = T.TaskID AND TGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
END
END
PRINT 'Inner Item Select:' + @InnerItemSelect
PRINT 'Inner Task Select:' + @InnerTaskSelect
SET @IDSelect = @IDSelect + @InnerItemSelect + ', '
SET @TSelect = @TSelect + @InnerTaskSelect + ', '
SELECT @Columns = STUFF(@Columns, 1, @separator_position, '')
END
---------------
That is only part of a large query that writs a SQL Query to a column in a Database. That Query (in the column) is just ran normally so I don't need to compile it each time I want to run it.
THe problem I have is @FieldName might be: ryan's field.
That apostrophe is killing me because the SQL keeps it as ryan's field, not ryan''s field(note the 2 apostrophes). I cannot do: REPLACE(@FieldName, ''', '''') because it's not closing the apostrophes. Is there an escape character that I can use to say only one: ' ?
I tried:
DECLARE @t VARCHAR(500)
SET @t = (SELECT [Name] FROM [Form].[Fields] WHERE FieldID = 1)
print @t -- @t will print: Ryan's Field
PRINT QUOTENAME('' + @t + '', '''')
but that gives me: 'Ryan''s Field'
Any help would rock. If I make any changes to the way this field is input into the DataBase, I know I will need to do a lot of re-work in many spots. WHich is why i'm trying to solve this on the SQL side.
View 4 Replies
View Related
Oct 24, 2008
I'm cleaning up a column in my table and getting rid of special characters.The only think I can't get rid of with the REPLACE function is single quotes.I'm doing aUPDATE TableSET Column = REPLACE(Column,'''','') --that's four single quotes then two single quotesBut the single quotes in my column wouldn't go away.I know that
DECLARE @string varchar(50) = 'test''s strings'SET @string = REPLACE(@string,'''','')SELECT @string
View 19 Replies
View Related
Jun 3, 2014
I just learned to use following query to convert columns to rows separated by comma.
Here is that :
Declare @list varchar(max)
select @list = isnull(@field+ ',','') + columname from table1
select @list
This produces the output I want but I'm confused with the statement. I just learnt it by heart. I don't know the meaning of it particular for the statement "select @list = isnull.............. from table1" . What exactly it does to give the desired output?
View 2 Replies
View Related
Feb 15, 2008
Hi I have a table named UserMaster having a column Sex for male and female.Sex MaleMaleFemaleFemaleFemale I want to replace all Male with Female and all Female with Male with a single query. Can any one please help me out
View 4 Replies
View Related
Jan 15, 2008
I can think of ways to resolve this issue, but I am wondering if there is a standard practice or some setting that is used to ensure that text containing a single quote, such as "Bob's house", is passed correctly to the database when using a SqlDataSource with all of the default behavior.
For example, I have a FormView setup with some text fields and a SqlDataSource that is used to do the insert. There is no code in the form currently. It works fine unless I put in text with a single quote, which of course messes up the insert or update. What is the best way to deal with this?
Thank you
View 10 Replies
View Related
Jun 21, 2012
I am trying to replace all special characters in a column with one special character.
Example:
Table: dbo.Employee
Column: Name
Name
-------
edwardneuman!"<]
mikemoreno)'>$:
JeffJensen"?>"
I am trying to get the namepart to the left of ANY special character. To achieve this, I am thinking of replacing all the special characters with a single special character so that I can find the first occurrence of that special character and grab left of the special character (SUBSTRING/CHARINDEX). This way I don't need to loop through all the special characters.
I am expecting the following results:
Name
-------
edwardneuman<<<<
mikemoreno<<<<<
JeffJensen<<<<
View 9 Replies
View Related
May 10, 2015
Here is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
View 4 Replies
View Related
Jul 26, 2002
Hi,
I need to write a single replace sql as folows:
I have a string consisting of numbers seperated by a space. Some of the numbers are suffixed with a star like this: '1 12* 5 7*'
I need to remove those numbers that are suffixed with a star. In other words, I need an output as follows: '1 5'
any help would be appreciated
Thanks
View 3 Replies
View Related
Jul 26, 2002
Hi,
I need to write a single replace sql as folows:
I have a string consisting of numbers seperated by a space. Some of the numbers are suffixed with a star like this: '1 12* 5 7*'
I need to remove those numbers that are suffixed with a star. In other words, I need an output as follows: '1 5'
any help would be appreciated
Thanks
View 1 Replies
View Related
Jul 26, 2002
Hi,
I need to write a replace sql in TSQL as folows:
I have a string consisting of numbers seperated by a space. Some of the numbers are suffixed with a star like this: '1 12* 5 7*'
I need to remove those numbers that are suffixed with a star. In other words, I need an output as follows: '1 5'
any help would be appreciated
Thanks
View 3 Replies
View Related
May 7, 2008
I've got a simple table with a column called html. In the html column I need to replace all occurances of <BR> with <br />
Can I do this with an update?
Thanks
View 1 Replies
View Related
Jun 3, 2014
if I have this string "ABCD test love", is there a way I can get rid of all text after "test" so that it becomes "ABCD test".
View 2 Replies
View Related
Mar 2, 2000
Hi,
I have a table where this one column(varchar) has rows of data with a period at the end of the string. Is there any function I could use to remove all the periods?
For eg:
bhjio.
shtpl.
should become:
bhjio
shtpl
Thanks
View 1 Replies
View Related
Jul 12, 2007
Hello
with the following query :
SELECT words FROM T1
i get :
A,B,C
how can i get
A > B > C
something like String.Replace(words , ',' , ' > ')
thank you
View 3 Replies
View Related
Jan 15, 2008
Hi experts,
I need to do an update on a field which will update 3 characters within a string. Right now I have '000111000333'. I want to replace '000' starting from the 7th character space with '222'. The first set of zeroes will remain unchange. I want the final outcome to be '000111222333'. How can I do this?
I have tried searching this forum and could not find anything. the closest thing I can find is http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81890#360936.
Thanks for the help!
View 2 Replies
View Related
Jun 25, 2015
I have employee members like
"firstname , secondname" in my dimension
i want remove , and display it like "firstname secondname"
i can do the same in sql server but i have to do it in mdx
View 4 Replies
View Related
Nov 12, 2007
Hi,
I am trying to replace a string column with 'Y' or 'N' in my SSRS report. I tried the following expressions, but no one works for me.
=IIf(Fields!ExpectedWords.Value Is Null, 'Y', 'N')
=IIf(Fields!ExpectedWords.Value = '', 'Y', 'N')
=IIf(IsDBNull(Fields!ExpectedWords.Value), 'Y', 'N')
Would you please tell me what's wrong? Your suggestions would be appreciated.
Thanks alot.
View 3 Replies
View Related
Aug 7, 2007
Hi,
I'm loading millions of rows into a database table. This data contains a lot of dedcimal values of 4 or 6 decimal places somtimes more.
I've found that storing them as floats is inaccurate, and I'm storing as decimals of the appropriate length.
I'm concerned whether this is the right way to do this ?
Can anyone make any suggestions, perhaps an article that I should take a look at ?
Sean
View 2 Replies
View Related
Apr 6, 2006
'000000' is a string with 6 charaters
Requirement:
I want to Replace 0 with 1.
Replace 0 to 1 at position 3 output= '001000'
Replace 0 to 1 at position 5 output= '000010'
View 2 Replies
View Related
Sep 13, 2004
hi,
In the result of a function in my query, there are negative numbers.
How do I replace them with a 0 or is there a function like ISNULL that replaces the values that are negative?
thanks,
maarten
View 1 Replies
View Related
Apr 1, 2013
I need to separate the data from one column in to two columns based on the transaction type TRAN_TYPE (C is credit, D is Debit)
TRAN-TYPE| AMOUNT
C 20.00
D 30.00
C 50.00
To do that I have this code:
select
CASE WHEN TRAN_TYPE = 'D'THEN CAST (ISNULL(amount, 0) as varchar (30)) end as DEBIT,
CASE WHEN TRAN_TYPE = 'c'THEN CAST (ISNULL(amount, 0) as varchar (30)) end as CREDIT
FROM HISTORY
And my output is:
DEBIT | CREDIT
----------------
NULL | 20.00
------|-------
30.00 | NULL
------|-------
NULL | 50.00
------|-------
how to replace the null values with 0
View 6 Replies
View Related
