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.
Hi! I recently was confronted with a problem where a piece of text that was included in many NTEXT column values in a table needed to be replaced with another piece of text. You can't issue normal REPLACE statements against NTEXT columns, so this seemed to be a bit of a challenge €” issuing a REPLACE() against a TEXT or NTEXT column in SQL Server yields error
Server: Msg 8116, Level 16, State 1, Line 1Argument data type ntext is invalid for argument 1 of replace function.
For Example: I want to replace string <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]> with <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]> in NTEXT column values in a table.
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?
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?
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.
Hi all, In a sql query I need to replace 0 (zero) with "Not rated" ...Can some one help me to do this.In short: how to replace a integer value to a string in a query? is it possible?Thanks for the HelpRamesh
Please help me with the sql script to manipulate the string data:
I need to add <Text> at the beginning and end of the string. I also need to add <option> before the first and after last occurence of the <Option> string. The original string
SELECT CASE WHEN CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1) > 0 THEN REPLACE(Supervisor, SUBSTRING(Supervisor, CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1), LEN(Supervisor)), '') ELSE Supervisor END AS NewSupervisor from data d
However, I stumble when Middle Name exists somewhere in the name as Replace function repalces every occurrence of the string. For ex: "Allen, Gary G" becomes "Allen,ary"
Do we have any way to say sql to replace after certain index?
I have a script that loops through a series of tables to send data to a table from each of the tables. My issue is that not all tables have the columns I need in them. What I would like is to replace the column with another column when it does not exist. Something like below
Select Misisng_Column(A.Name, replace with B.Name) as Name FROM SomeTable A Cross Join (Select Name FROM AnotherTable) B
AnotherTable has one record in it. To avoid a Cartesian issue. Like I said just an example
In my real script the table aliased as A is from a list of tables in a sys.tables query that loops through to the end.
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.
I have a string column in a DB where it's values contain the following midway through the string ([DOCUMENTGUID] is a uniqueidentifier that is different for each row):
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.
We have a legacy database that have hundreds of stored procedures.
The previous programmar uses a string like servername.databasename.dbo.tablename in the stored procedures. We now have migrated the database to a new server. The old server is either needed to be replaced by the new server name, or remove it.
I don't know why he used servername as part of the fully qualified name, we don't use linked servers. So I think better removing the servername in all the stored procedures.
I know I can do a generate script, and replace the text and then use alter procedure to recreate all the stored procedures. But since hundreds of them, is there a programmatically way to replace them?
I'm a newbie to SSIS. While experimenting with it I've encountered an issue which I'm hoping someone of you could help me out with. I have need to make a specific transformation which as output would have to produce rows with a new calculated column that replaces single column from input. New column has different data type than input column it is replacing. I've used Derived Column Transformation (DER) to do the first part of the work - appending new column and calculating its value (based solely on value from single original column that has to be replaced). Question is how should I do second part, task of removing no longer needed column from the pipeline? I've tried in DER instead of Derived Column being added as new column, selecting Replace 'column' but as it seems it is meant to replace only column data and not column data type (what I've expected). I've also tried using Copy Column Transformation (CPYC) but as it turns out CPYC transformation just (logically) duplicates data in the pipeline with optional different allias.
I'm trying to find a specific string (a name) and replace it with another inside of a VARCHAR(7000) field. Unfortunately, there are names like Ted and Ken that I'm trying to replace. I would like to leave words like Broken, admitted, etc... intact.
UPDATEtbl SETBody = LEFT(REPLACE(tbl.Body, pm.OldFirstName, p.FirstName), 7000) FROM Table tbl JOIN Person p ON p.PersonID = tbl.PersonID JOIN PersonMap pm ON pm.PersonID = p.PersonID AND LEN(pm.OldFirstName) > 2 WHEREtbl.Body LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%
'The problem I'm running into is that the '[, ]%' in the LIKE excludes any record that ends with the FirstName because it is requiring either a space, comma or period after the name. Is there some way to add an empty string to the list of acceptable characters as that would cover any scenario in the data? I would prefer not to add all characters except space, comma and period, but I guess I could do that.
Table 1 --------- id | Name --------- 1 | John 2 | Debbie 3 | Kim 4 | Mary
Table 2 --------- id | Name --------- 654 | John 415 | Debbie 68 | Kim 289 | Mary
Table 2 id's values are all messed up and I need to replace them so that they look exactly like in Table one, my guess is that query will have to replace value of "Id" based on the "Name" column, I hope I made myself clear.
I've searched through this forum, and am extremely disappointed that something as simple as this can't be done without resorting to a script component (which I refuse to do):
I have a Lookup transformation that returns a GUID value -- or not, so I've configured its error output to ignore failure. That should leave a NULL in the output column, and I should be able to chain a Derived Column transformation that replaces the Lookup output column (e.g. foo) with an expression like:
isnull(foo) ? newid() : foo
But there's no newid() function! Who was the genius that decided SSIS should implement different expressions than Transact SQL? Is there a workaround, like (DT_GUID)GETDATE()?
Or do I have to craft a SQL statement in the Lookup that guarantees a non-NULL result?
How do I improve a 3 column, composite clustered index on a large table when the developer insists there is no other way to achieve uniqueness? They say a uniqueindentifier column will not work.
Hi, I was wondering if it is posible as in derived columns to replace an input column with the output one in Data Conversion? What I can see that when a data conversion is done, only we can do it in a new column and not in the same one we are using as input. Do you know if this it is posible?} Thanks for your help!