How To Write A T-SQL Script To Replace Val() In MS Access?
Oct 11, 2006
I am converting an MS-Access DB to SQL-Server, and one of the queries I'm
converting uses the VB Val() function in the ORDER BY clause. Is there any
T-SQL function that can mimic this? CAST and CONVERT return errors because
some of the data is non-numeric. The column I am ordering is a varchar(5) with
ID number 'ZZ0001', 'CC0002', 'AA0003' etc . I would like the dataset to be sorted by number only :
'ZZ0001'
'CC0002'
'AA0003'
Thank you
View 7 Replies
ADVERTISEMENT
Aug 24, 2001
I can't figure out how to replace a single digit entry in my database with a new digit. i want to replace in the alarms.severity all values of 4 with a new value of 2. For some reason the script I wirte won't get past the digit 4 in the statement. Anyone have a script they can let me try?
View 1 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
Jul 21, 2006
My company currently has about 20-25 Ms Access Database that they want toreplace the FE with .net and the BE on SQL.This will be done using Visual Studio 2005. Once the FE is converted to .netand the BE is SQL they all will be accessed through our intranet (sharepoint).I work in Ms Access and intermediate at VBA and just learing SQL through theENTERPRISE MANAGER SCREEN.I am just now looking at what Visual Studio 2005 is, but can some one tell mehow this will all connect?What is the typical route for this process?Ms access to SQL - upsizing wizard or SQL importing?????Ms Access FE to .net - summarize how this is done in visual studio (user facerebuilt) then placed on sharepoint?Can anyone sum this up?--Message posted via http://www.sqlmonster.com
View 4 Replies
View Related
Aug 24, 2001
I have a database that has been working in MS Access 97 for a couple of years. I just moved the tables up to our SQL Server (7.0) and now I get "Write Conflict" errors when I try to update certain records. The table has a unique primary key and I have upgraded my MDAC to 2.50. Nothing seems to help.
The records that get the Write Conflict tend to be ones that are the newer ones in the database. Not all records have this problem. I can update the records by going to Enterprise Manager and typing data directly in the tables.
The detail on the error message says "This record has been changed by another user since you started editing it." I am the only one using the database.
Any ideas?
View 3 Replies
View Related
Jul 22, 2004
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)
SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
View 5 Replies
View Related
Jul 20, 2005
I can access Microsoft Access database via ODBC on Windows 2000 usingC++. Now, I would like to use the SAME piece of code to access MS SQLon Windows. Can I achieve this without any modification to my existingcodes?Then, I would like to use the SAME piece of codes to access Oracleon Windows? Is it possible?Thanks in advance!
View 1 Replies
View Related
May 30, 2006
Hi,I'm accessing a pair of databases with ASP/ADO,and using stored procedures on the first access.The first access works OK - everything gets writtento where it's supposed to be.On attempting to write to a pair of database tablesin the second database (second access attempt),the server crashes, as does the app, and I get the following error:--------------------------------------------------------------------Server Error in '/Webfolder01' Application.Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)--------------------------------------------------------------------After this, I have to go into the services panel and restart SQL 2005.This doesn't happen if I use small test data sets in the first access,and I can comment out the second access attempt, and the first accessexecutes just fine with both the large and small data sets,so I'm thinking that this has something to do with synchronisation:Perhaps the second attempt is being made before the server is ready.Is there something that I need to do to make certain that SQL 2005is ready to receive data?I'm using SqlBulkCopy with both accesses, but I don't see how thatcould be a problem.--------------------------------------------------------------------[SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857370 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734982 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) +556 System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) +164 System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) +34 System.Data.SqlClient.TdsParserStateObject.ReadBuffer() +30 System.Data.SqlClient.TdsParserStateObject.ReadByte() +17 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +59 System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() +1327 System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer (Int32 columnCount) +916 System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) +176 System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table) +6 Database_control.DB_ctrl_class.load_datatable_to_DB_table(DB_ref_class src_table) in i:Virtual WebfoldersDBctrl.cs:978--------------------------------------------------------------------THANK YOU VERY MUCH!!!
View 4 Replies
View Related
Apr 7, 2015
How can I create a SQL authentication account with read-write access to only 1 table in a SQL database.
View 1 Replies
View Related
Jul 12, 2006
Hi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
View 12 Replies
View Related
Oct 16, 2007
Hi all i have a question regarding sql, i want to replace some characters...
any knows simply how to do this?
I want to replace "999-25000-69" by "9992500069"
grtz
View 1 Replies
View Related
Nov 28, 2006
i got a 100k rows column contain first name + last name. but half of them are got comma between first and last name. how can i update and remove all the comma. can anyone provide a statment please thanks so much
View 2 Replies
View Related
Aug 15, 2006
can I use a replace on text type field?
View 1 Replies
View Related
Apr 11, 2006
hi
when i execute ...
select * from Members where address = '6257 Rockwell's'
ERROR :
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string '
'.
Kindly help me how to solve '
View 2 Replies
View Related
Nov 15, 2006
i just wanna replace the last word 'roa' to 'road'
what is wrong with my code. I dont relaly see any problem. But it's replaceing all the 'roa' to 'road'.
declare @table table(ad_str1 varchar(20))
insert @table
select 'street road roa' union all
select 'street street'
select ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))) from @table
update @table
set ad_str1 = replace(ad_str1, ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))), 'road')
where ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))) = 'roa'
select * from @table
View 9 Replies
View Related
Nov 29, 2007
I have a colunm in a table that has a list of email address.
I want to remove a certain email address from that colunm, but it is in 54 different rows. What is the best way to do this ?
View 2 Replies
View Related
Dec 12, 2007
Dear all,
here is one query,
IF EXISTS (SELECT SUM(DEBIT_AMOUNT) FROM @Ledger
HAVING SUM(DEBIT_AMOUNT)<>SUM(CREDIT_AMOUNT))
i'm trying to tuning myprocedures. i think having is not good enough to use.
how can we replace having in this case.....
thank you very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 6 Replies
View Related
May 28, 2008
Hi everyone,
I have wriiten a query that works great in sql server but MS Access does not support EXCEPT. I have to rewrite this query using 'IN' or 'NOT IN' but can not figure it out because it must be compared on all fields. I'm sure this will require multiple subqueries because 'IN' must return only one field but I can not rap my brain around it. Could someone please help with this? Here is the 'EXCEPT' query I know works.
Code Snippet
SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
sh.Balance, sh.Available_Bal FROM
(((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
Participation_Type ON ac.Participation_Id = Participation_Type.ID)
WHERE ac.INDIVIDUAL_ID = 5249
Except
SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
sh.Balance, sh.Available_Bal FROM
(((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
Participation_Type ON ac.Participation_Id = Participation_Type.ID)
WHERE ((ac.participation_id <> 101) AND (sh.share_type > 49 )) AND (ac.Individual_Id = 5249); This code gets me close using 'IN'.
Code Snippet
SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
sh.Balance, sh.Available_Bal FROM
(((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
Participation_Type ON ac.Participation_Id = Participation_Type.ID)
WHERE (ac.INDIVIDUAL_ID = 5249) AND sh.member_nbr not in
(SELECT sh.member_nbr from Share sh inner join accountindividual ac on sh.member_nbr = ac.member_nbr AND sh.account_nbr = ac.account_nbr
WHERE ac.participation_id <> 101 AND sh.share_type > 49) AND sh.account_nbr not in
(SELECT sh.account_nbr from share sh inner join accountindividual ac on sh.member_nbr = ac.member_nbr AND sh.account_nbr = ac.account_nbr
WHERE ac.participation_id <> 101 AND sh.share_type > 49)
View 4 Replies
View Related
Sep 13, 2006
To ensure I don't leave orphans floating around in tables when records get deleted (values from one record might link to values in another) how do I find and possibly replace values in tables?For example, if I have a unit of measure table and want to delete the value "inches", how do I look in other tables to find this value and give the user the option to cancel or clear it out. If I don't it will cause controls bound to that value like the dropdownlist to throw an error.
View 1 Replies
View Related
Feb 18, 2007
Hi all,
I made a migration operation and when I am looking inside database I can see the <BR> tags.
The question is how to write a query that replace all of this occurances of <BR> inside the colum table, with CLRF which is a new line code.
Thanks in advance.
View 2 Replies
View Related
Aug 22, 2007
I have the following:----------------- WHILE PATINDEX('%,%',@Columns)<> 0 BEGINSELECT @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: ' + @FieldNameBEGINIF @FieldTypeID = 1 OR @FieldTypeID = 2 OR @FieldTypeID = 3 OR @FieldTypeID = 9 OR @FieldTypeID = 10 OR @FieldTypeID = 7BEGINSET @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 + ''' 'ENDELSE IF @FieldTypeID = 4 OR @FieldTypeID = 8 --DropDownList/RadioButtonlistBEGINSET @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 + ''' 'ENDELSE IF @FieldTypeiD = 5 --CascadingBEGINSET @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 + ''' 'ENDELSE IF @FieldTypeiD = 6 --ListBoxBEGINSET @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 + ''' 'ENDELSE IF @FieldTypeID = 11 --UsersBEGINSET @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 + ''' 'ENDELSE IF @FIelDTypeID = 12 --GroupBEGINSET @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 + ''' 'ENDENDPRINT 'Inner Item Select:' + @InnerItemSelectPRINT 'Inner Task Select:' + @InnerTaskSelectSET @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: ' ?Would the only solution be to put: ryan''s field into the Database, and just format it properly on the output? Thanks.
View 4 Replies
View Related
Sep 23, 2007
Hi,
I am having a situation where I need to update a column in my SQL table that contains a link to an image file. Basically ...
I have this stored in a column IMAGESRC
Project/aa11be5d-dd9e-48c8-9d8c-6a972e996b28/ProjectImages/702d_2.jpg I need to change it to this
Project/NEWUSERID/ProjectImages/702d_2.jpg
How can I accomplish this in SQL???
thanks in Advance
Dollarjunkie
View 2 Replies
View Related
Oct 30, 2003
Hi,
I have a table with a field called productname, and it has about 5000 rows, and within that about 1000 have a productname that has 'NIB' in the name, ie "My Product NIB DVD" and I have been asked to replace 'NIB' with 'New' ie "My Product New DVD" Can I do this in SQL using an Update statement? Or do I have build something in maybe asp.net to use a replace function to change the name.
Thanks
View 9 Replies
View Related
Feb 21, 2004
I'm not sure how to use REPLACE here. The Query following REPLACE returns a string whose format is A B C D and I'm trying to convert it to 'A','B','C','D' I'm doing something wrong because query analyzer doesn't like something about the way I've written this.
WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @UserID) AND (Portfolio_Name = @Portfolio_Name),'''','''''')
Entire SPROC
-------------------------------------------------------------------------------------------------
CREATE PROCEDURE _premium_BSH (@Portfolio_Name NVarChar (50), @UserID int, @Symbol VarChar (1500)) AS
SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Industry.Industry, a_Sector.Sector, a_Quarter_Index.Period, a_Technical_Signals.Signal,
a_Technical_Signals.[Date], a_Financials.Revenue, a_Financials.Income, a_Financials.EPS, a_Financials.Margin_Net AS [Net Margin],
a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo, a_Hyperlinks.MSN_10Qs AS Financials, a_Hyperlinks.MSN_events AS Events,
a_Hyperlinks.StockCharts AS Technicals
FROM a_Financials INNER JOIN
a_Hyperlinks ON a_Financials.Yahoo_Main = a_Hyperlinks.Yahoo_Main INNER JOIN
a_Industry ON a_Financials.Industry = a_Industry.Industry INNER JOIN
a_Sector ON a_Financials.Sector = a_Sector.Sector INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol INNER JOIN
a_Technical_Signals ON a_Name_Symbol.Symbol = a_Technical_Signals.Symbol INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @UserID) AND (Portfolio_Name = @Portfolio_Name),'''','''''') AND (NOT (a_Technical_Signals.Signal IS NULL)) AND (a_Quarter_Index.Period = '2003 Q3')
ORDER BY a_Name_Symbol.Name, a_Technical_Signals.Signal
GO
View 8 Replies
View Related
Apr 30, 2004
Does anybody know how to search through all stored procedures (in SQL Server 2000) and find and replace text without having to open each one individually in Query Analyzer and doing so one at a time?
That would be so time consuming. I want to be able to change my table names, but I have so many stored procedures allready using the old names. To go and find each name and replacing them is a task I don't want to even try and do.
Thank you to whomever can help.
Alec
View 1 Replies
View Related
Aug 10, 2004
I am using MSDE. In this I have a field that contains a desciption. Prior to saving to the db, I replace all vbcrlf's to <br />'s. That works fine when displaying in HTML, but when I display it in a datagrid, I want to replace the <br />'s with vbcrlf's.
I thought I might be able to do it with a replace function in my SQL query, something like "Select ID, replace(Description, '<br />', vbcrlf) as Description". This produces an error that vbcrlf is not a fieldname. So I tried "select replace(Description, '<br />', 'xxx') as Description", and while this did not create an error, neither was the text replaced.
Am I not using the replace function correctly? Is there another way in which I might accomplish the task?
Dim ConnectionString As String = "server='(local)'; trusted_connection=true; database=dbname"
Dim CommandText As String
'Command text is greatly abreviated for this discussion.
CommandText = "Select ID, replace(Description, '<br />', vbcrlf) as Description"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
Dim ds As New DataSet()
Dim dv as new dataview()
myCommand.Fill(ds)
DataGrid1.DataSource = ds
DataGrid1.DataBind()
...
...
...
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 3 Replies
View Related
Apr 18, 2000
Hello
Can anyone tell me how to replace a single apostrophe in a record with a double apostrophe (in Query Analyzer, SQL7)? I've tried "select replace("d'","d'","d''")FROM RESORT" but it doesn't UPDATE the table.
Suggestions gratefully received!
Mark
View 1 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
Mar 28, 2006
I have a need to evaluate a parameter to use like keywords. (Not my server, so indexing is not available.) I can get the first scenario to work, but wanted to see about getting the syntax so I wouldn't have to exec the statement. If I run the first execute statement, I get three rows (accurate). If I run the second statement, I get no rows. Anybody help me out with the syntax? I've tried so many variations, I'm lost. Many thanks.
declare @key as varChar(50), @sql as varchar(1000)
set @key='flow afow'
set @sql='select myID from myTable where [title] like ''%' + replace(rtrim(ltrim(@key)),' ','%'' or [title] like ''%') + '%'''
exec(@sql)
select myID from myTable where [title] like '%' + replace(@key,' ', '%''' or [title] like '''%') + '%'
View 2 Replies
View Related
May 17, 2002
I'm trying to replace some text in a field. It looks something like this:
63.73 Avail %= 36.27 Used space MB = 2609.34375 Free space MB = 1485.34765625
I only want the 63.73 number at the beginning. How do I put a wildcard to replace everything after it, starting with 'Avail...'?
View 4 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
Feb 21, 2007
Anyone find any good readings on how to do a regular expression to replace text?
Basically trying to figure out how to replace text in columns (replace quotes " with the word inches. so 12" will be 12 inches)
View 5 Replies
View Related
