Hi all,
I have a stored procedure that generates the following SQL WHERE clause
UserName LIKE 'Adi234%' AND Fname LIKE 'David%' AND LName LIKE 'Justin%' AND
It is good except that it i can not remove the last AND which is not neccessary at the end of the clause.
I want to remove the last AND that come up at the end, my code places AND after each data field(UserName, Fname, Lname) .
Thanks
Can unwanted characters (e.g. control codes) be replaced or removed in varchar fields during extraction inside DTS package?
SQL Server/SSIS 2005.
Thanks, Andrei.
Hi ,
I have lot of SQL logins which do not have access to any databases . They do not show any database access when i check their properties .
My server is SQL 7.0 with SP 3 .
I want to clean up those logins with the help of script instead of deleting every login manually which is time consuming as i have to check the database access before deleting the login.
Is there any where script available or any other way to delete these unnecessary logins ? Also will it be a problem on the SQL Server if i delete those logins at one command ?
Any info. will be helpful to me.
Thanks
Sandeep .
hi
we get ASCII data inserted into a SQL Server database by ODBC connection from an old UNIX system.
Example: INSERT INTO test.db VALUES ('123abc', '456ПРО')
All characters > 128 are converted to "?" automatically.
We tried to setup the database to the appropriate codepage, but we allways get "?" inserted.
I am creating a report query that returns all unreconciled P/O lines. I am near completion but I am unable to find a way to remove the reconciled records.
I have included a script to produce some sample table, data & query.
The recordset dispalys 6 rows. All reconciled Supplier Invoices are duplicated and have transaction codes 40, 50 and reconcile code of 9 (5024, 921689471).
All unreconciled only appear once and have transaction codes 40 and reconcile code of 0 (4835 & 921978016). These are the only records that I want to show.
CREATE TABLE [dbo].[Purch_Ledger](
[EPDIVI] [nvarchar](3) NULL,
[EPSUNO] [nvarchar](10) NULL,
[EPSINO] [nvarchar](24) NULL,
[EPDUDT] [numeric](8, 0) NULL,
[EPTRCD] [numeric](2, 0) NULL,
[code].....
Whatever I try I cant find a way to get rid of the unwanted records.
I have a third party app that passes parameters (main dataset query) from the app to SSRS. Â Unfortunately, when the parameters are passed from the app they will contain equal signs ("=") in front of each parameter. Â For example, the parameters that may be passed to the main dataset query should be:
"HYDRO, OKO, ONEPL, TECHNI"
However, the parameters that are passed from the app, get to SSRS as:
"=HYDRO, =OKO, =ONEPL, =TECHNI"
Again, this is for the main dataset query and there may be one parameter or there may be any number of them.
Basically, I need to strip the "=" signs from the parameters. whether there is one parameter or ten.
I believe that I will need a custom function to strip the equal signs?
How do I remove the first 5 characters from a column?
I have a list of id's -
9999925173
9999924037
9999924063
9999924053
9999924053
0
0
9999924053
9999924049
9999924037
9999944659
9999924053
0
9999924032
9999924037
9999924053
For some reason, we add a 99999 to our ID's and in order to compare to the customers data, I need to remove the 99999 (if they exist) from the column.
How would I do that? LEN?
Here's the output I'm looking for -
25173
24037
24063
24053
24053
0
0
24053
24049
24037
44659
24053
0
24032
24037
24053
Thanks
Susan
I have the following sql statement:
SELECT FTE_CLASS_GROUP_NBR_DSCR
FROM dbo.DLIST_FTE_CLASS_GROUP
WHERE FTE_GRP_ID IS NOT NULL
This is an example of the result returned:
9999/00 Some lenghty text is displayed after the numbers
I want to trim everything after 9999/00
Is there a way to use rtrim to remove the characters after the numbers or another method?
Thanks,
-D-
There are unwanted characters(''','/','&'.. etc) in column.
I need to remove these characters
I have data with invalid character in my source table. I'd like to remove the replace/remove the invalid characters. What function should i use?
e.g.,
0233‚¬
20116267 ‚¬{ ‚¬´E‚¬
I'd like to get only
0233
20116267.
I'd appreciate your inputs!
Thanks,
OM$
Hi
I have a table with a few hundred emailadresses. How can I delete all quotes (') from the addresses, so that 'email@email.com' is replaced as email@email.com.
Thank you
zipfeli
I have built a SSIS package that reads in data from a SQL Server 2005 source database into a flat file destination. The Row Delimiter is {CR}{LF}. The Column Delimiter is Tab {t}.
The data being read from the SQL Server database contains both {CR}{LF} and Tab {t} characters in various fields on several rows.
How can I process the input data from the SQL Server to remove these characters before passing it to the destination output file?
Sorry if this is obvious to all, but I am only just starting with SSIS...
Many thanks
Adrian
best possible way to remove all the characters after a 3rd repetition of a character?
For Example:
I want 10.0.1600.22 to be 10.0.1600
Everything after and including the '.' to be removed.
I understand Substring_Index() is not available whats the other options?
I am looking for a function or way to return only results which does not include appended characters to order numbers.
For instance, below is a list of order numbers. I only want the order number that is SO-123456
OrderNumbers
SO-123456
SO-123456-01
SO-123456-2
SO-123457
SO-123457-1
SO-123457-02
SO-123458
I would like my query to only show the below results
SO-123456
SO-123457
SO-123458
What functions or query methods could achieve this?
I was hoping for something similar to RTRIM but that is only specific to white space.
I want to remove special characters from a string in sql like <?> in a column value in a table.
View 1 Replies View RelatedI have a table that is riddled with weird characters. So far I have found an escape character for PDF files and a trademark sign. These characters are crashing my SSIS packages. I am able to remove these characters with an update script...
Update TABLE
set LEAD_NOTES__C = Replace(LEAD_NOTES__C, nchar(65533) COLLATE Latin1_General_BIN2, '!');
Update TABLE
set LEAD_NOTES__C = Replace(LEAD_NOTES__C, nchar(1671) COLLATE Latin1_General_BIN2, '!');
This works fine, but my question is...
I would like to write a script that removes all foreign characters with the exception of the normal characters like (@,#,$,%,etc). I need a dynamic process that handles this so I am not losing time sifting through over 20,000 rows of data and changing my update script to remove a specific column. Although this method works, I would prefer a dynamic query. I intend to wrap this in a stored procedure that loops through all columns in a table (as parameter).
Hi to all,
I am having a string like (234) 522-4342.
i have to remove the non numeric characters from the above string.
Please help me in this regards.
Thanks in advance.
M.ArulMani
Hi to all,
I am having a string like (234) 522-4342.
i have to remove the non numeric characters from the above string.
Please help me in this regards.
Thanks in advance.
M.ArulMani
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
I'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.
I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.
There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.
Special Characters are defined as #%&*:<>?/{}|~ and ..
While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.
Example data:
Tablename = Items
ItemID Filename ListID
1 Badfile<2015>.docx 15
2 Goodfile.docx 15
3 MoreBad#.docx 15
4 Dog&Cat#17.pdf 15
5 John's "Special" Folder 16
The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.
In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.
My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.
I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.
Hi All,I am trying to delete rows from a table using a SQL statement similarto this:DELETE FROM TableA where ID1 IN(Select ID1 From TableB where ID2>= @min and ID2<=@max)Basically I want to delete all rows from TableA that have an ID in arange in TableB. This is done in a stored proc.When I look at the execution plan, it is using the indexes as I wouldhope for. The problem is that it is doing a sort which accounts for73% of the cost. I do not need to sort the results. I don't care whatorder they are deleted in.How can I prevent the sort from occuring? I need this delete to occuras fast as possible.Thanks In Advance
View 4 Replies View RelatedI've created an asp.net page that takes the content of text boxes and writes them to a sql table.
The problem is that when I examine the resulting data in the SQL database, I find that the fields written to have had padding added (up to the maximum size of the fields).
I was under the impression that fields of type NVARCHAR did not store padding (only the no of characters being stored).
I've checked it's not the text boxes on the aspx page by explicitly posting values instead of the boxes content and the same thing happens.
Help
example of function i'm using to post data:
Function AddNews(ByVal Category As String, ByVal ApplicRole As String, ByVal NewsType As String, ByVal Description As String, ByVal News As String, ByVal Hyperlink As String, ByVal Email As String, ByVal BirthDate As Date, ByVal KillDate As Date, ByVal Parent As String) As Integer
Dim connectionString As String = ConfigurationSettings.AppSettings("AuthentConnection")
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [News]([Category],[ApplicRole],[NewsType],[Description],[News],[Hyperlink],[Email],[BirthDate],[KillDate],[Parent]) VALUES (@Category,@ApplicRole,@NewsType,@Description,@News,@Hyperlink,@Email,@BirthDate,@KillDate,@Parent)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_Category As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Category.ParameterName = "@Category"
dbParam_Category.Value = Category
dbParam_Category.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Category)
Dim dbParam_ApplicRole As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_ApplicRole.ParameterName = "@ApplicRole"
dbParam_ApplicRole.Value = ApplicRole
dbParam_ApplicRole.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_ApplicRole)
Dim dbParam_NewsType As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_NewsType.ParameterName = "@NewsType"
dbParam_NewsType.Value = NewsType
dbParam_NewsType.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_NewsType)
Dim dbParam_Description As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Description.ParameterName = "@Description"
dbParam_Description.Value = Description
dbParam_Description.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Description)
Dim dbParam_News As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_News.ParameterName = "@News"
dbParam_News.Value = News
dbParam_News.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_News)
Dim dbParam_Hyperlink As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Hyperlink.ParameterName = "@Hyperlink"
dbParam_Hyperlink.Value = Hyperlink
dbParam_Hyperlink.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Hyperlink)
Dim dbParam_Email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Email.ParameterName = "@Email"
dbParam_Email.Value = Email
dbParam_Email.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Email)
Dim dbParam_BirthDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_BirthDate.ParameterName = "@BirthDate"
dbParam_BirthDate.Value = BirthDate
dbParam_BirthDate.DbType = System.Data.DbType.Date
dbCommand.Parameters.Add(dbParam_BirthDate)
Dim dbParam_KillDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_KillDate.ParameterName = "@KillDate"
dbParam_KillDate.Value = KillDate
dbParam_KillDate.DbType = System.Data.DbType.Date
dbCommand.Parameters.Add(dbParam_KillDate)
Dim dbParam_Parent As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Parent.ParameterName = "@Parent"
dbParam_Parent.Value = Parent
dbParam_Parent.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Parent)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
Return rowsAffected
End Function
Here is a simplified version of my problem:
I am inserting data into a table using a stored procedure. The table has an identity column that increments with each insert. When I use erroneous data in the other fields the insert fails….no surprises there! But when the next insert occurs with valid data I find that my identity field has increased even with those inserts that failed, so my sequence has jumped a few numbers. How do I get the identity inserts to roll back if the rest of the data in a row doesn’t insert successfully?
Marcha x
Hello, I have a report that is based on a dynamic column. The column is specified by the user, and the column tyep may be text, datetime, or bigint. The user specifies the column name, and there is also a parameter that specifies the type. I allow my users to format the date via passing in a date format string, so my code looks something like:
=Switch
(
Parameters!primarycolumntype.Value = "text",
Fields(Parameters!primaryitemcolumn.Value).Value,
Parameters!primarycolumntype.Value = "date",
[DO DATE FUNCTIONS HERE, e.g. Year(Fields(Parameters!primaryitemcolumn.Value).Value)],
Parameters!primarycolumntype.Value = "numeric",
Fields(Parameters!primaryitemcolumn.Value).Value
)
The trouble I am running into is that I am getting invalid date conversion errors whenever a text value is being supplied - in other words, the code in the "DO DATE FUNCTIONS HERE" is being executed regardless of whether my primary column type parameter is text OR date. It would appear that all conversion fuctions are calculated at runtime prior to running other logic. If so, this is a bad thing for me, as that means I cannot mix data types in the same report and figure it out all dynamically. Is this true? I had always thought that the date code (in the example above, the call to Year()) would not be executed unless that statement in the switch statement was true. I've had the same luck with If statements, and Choose statements, and I've even tried using custom code.
I think my whole problem relates to a misunderstanding of how the functions are processed. Can anyone shed some light?
Thanks!
Michael
Hello ,
I have created a publication from SQL Server 7.0 and subscribed the publication it to one of the SQL 2000 server std. edition .
Now there are some entries in the subscription list at the subscriber which need to be deleted .
The list shows the name of the publication , the database name and the date .
I wanted to know hoe can we delete those unwanted entries. If i select the entry and right click on it , there is no delete option .
Are those entries to be deleted from one of the tables at the subscriber or the publisher ? If yes, then what is the name of the table ?
Any ideas ?
Thank you very much .
How do I remove old stored procedures that are no longer being used?
Thanks,
rm
I have some code that sorts a column logically even if it contains both numbers and text. But I've found that SQL converts certain characters automatically....
Here is the code...
SELECT * FROM TABLE
order by
case when isnumeric(fieldname)=1 then
right(replicate(' ', 10 ) + convert(varchar,convert(numeric(10),fieldname)),10)
else fieldnameend --10 is the length of the field
The issue I've run across is that if my column contains the following strings(5D000,5E000,4E001) it thinks they are numeric then the THEN part of the condition causes a typecasting error....
I found out that any certain combinations of number + E or D + 0. Does anyone know a way to get around this? Try it out select isnumeric('5E0303') will return 1
I wanted to remove an extra transaction log file that was no longer required, and ran the following against the database...
DBCC Shrinkfile (DB_Name_log2, Emptyfile);
go
alter database [Db_Name]
remove file DB_Name_log2;
go
I got a successful removal message. But if I go into the properties of the database, and click on files, it still shows up. Why is this and how can I get rid of it?
It shows up in sys.master_files as offline.
Hi All,
We've got a basic query that pulls a list of some parts out of our inventory database:
SELECT p21_view_inv_mast.item_id, p21_view_inv_mast.item_desc, p21_view_inv_loc.gl_account_no
FROM dbo.p21_view_inv_loc p21_view_inv_loc, dbo.p21_view_inv_mast p21_view_inv_mast
WHERE p21_view_inv_loc.inv_mast_uid = p21_view_inv_mast.inv_mast_uid
This returns a lot of info over three columns: "item_id" "item_desc" and "gl_account_no"
What I want to do is remove any entries where the "gl_account_no" shows as 011500000
I am by no means a TSQL person, I mostly run the canned queries that our vendor gives us so any help with this would be greatly appreciated.
Hi,
I have a need to create a report that has a graph at the top and a table at the bottom. The graph at the top can optionally be made hidden because they cause problems when exported to Excel as images. However, when I set the Hidden property of the graph to true, positions of all items on the report remain absolute. Meaning of course that the table that is located half-way down the page remains half-way down the page and there is a lot of nothing on the first half where the graph used to be.
It would be desirable to have the ability to have the table move up when the graph is not visible, however it obviously doesn't do it automatically and also refuses me the ability to change the position with an expression.
Any advice is appreciated, thank you!
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message.
How can I store over 16000 characters to sql table field (only one field) with language specific characters?
Thanks
Hi all,
I tried to remove AdventureWorksDB in the "Add or Remove Programs" of Contol Panel and I got the following errors: (1) AdventureWorksDB Error 1326: Error getting file security: CProgram FilesMicrosoft SQL ServerMSSQL1MSSQLGetLastError: 5. |OK| and (2) Add or Remove Programs Fatal Error during installation (after I clicked the |OK| button). Please help and tell me how I can solve this problem.
Thanks in advance,
Scott Chang
This a microsoft SQL 2000 server.
I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID.
Here is my problem:
Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.
I know how to get the user table names from the DB as follows:
SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U' and name like 'Elect_Sub%Daily'
What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".
delete from tableName where Date_Stamp = '2008-3-18'