RE: find_string in db
plug How about calling your Oracle sales rep and asking how they are doing on a compliant implementation of SQL/PSM? /plug I would, but he's too busy frolicking in a swimming pool full of 100-dollar bills to answer the phone right now. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211500 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: find_string in db
hey thanks guys. I already implemented a clunkier way, but I'd be interested in saving Chris' stored procedure until I'm good enough to implement it. I'll be implementing searches forever into the future. Why wouldn't it work in Oracle (which is my DB)? That's unlikely to work in Oracle ;-) On 7/6/05, Chris Terrebonne [EMAIL PROTECTED] wrote: Daniel, Included below is a stored procedure that you can use to search any or all columns in all tables. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211238 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: find_string in db
daniel kessler wrote: hey thanks guys. I already implemented a clunkier way, but I'd be interested in saving Chris' stored procedure until I'm good enough to implement it. I'll be implementing searches forever into the future. Why wouldn't it work in Oracle (which is my DB)? Stored procedures are not very portable (yet) between databases. The SQL standardization committee was late defining a standard for stored procedures (SQL/PSM in 1997?, basic SQL is from 1989/1992) and by that time the big three databases already had their own languages for stored procedures. Oracle uses pl/SQL (a dialect of SQL/PSM) while MS SQL Server uses TransactSQL which is very different. In a few years this will probably improve. SQL/PSM is modelled after the procedural language in IBMs DB2. So naturally DB2 already supports it and some of the smaller databases have done so for a while too. What is new is that both MySQL and PostgreSQL are working on a compliant implementation of SQL/PSM. So in a few years we will have MS SQL Server with a totally different procedural language, Oracle with an incompatible dialect, and a large group of other databases where stored procedures are portable. plug How about calling your Oracle sales rep and asking how they are doing on a compliant implementation of SQL/PSM? /plug Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211241 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: find_string in db
Thanks for the explanation Jochem. It doesn't sound all that optimistic. As for the plug, I'll at least add my noise to my System Administrator here on campus. Stored procedures are not very portable (yet) between databases. The SQL standardization committee was late defining a standard for stored procedures (SQL/PSM in 1997?, basic SQL is from 1989/1992) and by that time the big three databases already had their own languages for stored procedures. Oracle uses pl/SQL (a dialect of SQL/PSM) while MS SQL Server uses TransactSQL which is very different. plug How about calling your Oracle sales rep and asking how they are doing on a compliant implementation of SQL/PSM? /plug Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211244 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: find_string in db
Ahh, you're using Oracle. Yea, it's going to choke on that one. You could always try porting it. :) [EMAIL PROTECTED] 07/06/05 12:37AM That's unlikely to work in Oracle ;-) On 7/6/05, Chris Terrebonne [EMAIL PROTECTED] wrote: Daniel, Included below is a stored procedure that you can use to search any or all columns in all tables. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211251 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: find_string in db
I can loop through the columnList with a set of LIKE commands, but is there a way to search all columns without making 50 LIKE commands? If you really have to do this, I would say that the database was very poorly designed at the first place. However, you could try an SQL expression that would concatenate all 50 columns in one string, then look if this string is LIKE whatever. I hope the table is not too large however! -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211139 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: find_string in db
If you really have to do this, I would say that the database was very poorly designed at the first place. The database was not poorly designed or rather it may be but this request does not show it. This is a one-time request to see if any bad data was entered during the updating process. However, you could try an SQL expression that would concatenate all 50 columns in one string, then look if this string is LIKE whatever. I hope the table is not too large however! ok, great, thanks! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211142 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: find_string in db
This is a one-time request to see if any bad data was entered during the updating process. Ah okaaay, excuse me for the bad design ;-) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211143 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: find_string in db
If it's a 1 time request, I'd say just write the sql. If it's something that's going to be done regularly, I'd look into oracle's full text searches. (You're using oracle, right?) If you want to learn something new, you could probably write some pl/sql to look up the column names from the data dictionary and do a loop through those. If you want to be lazy I'd do select * from mytable in a cfquery, then dump myquery.columnlist, loop through that and and build your like statements, then use that to do the real query. On 7/5/05, daniel kessler [EMAIL PROTECTED] wrote: If you really have to do this, I would say that the database was very poorly designed at the first place. The database was not poorly designed or rather it may be but this request does not show it. This is a one-time request to see if any bad data was entered during the updating process. However, you could try an SQL expression that would concatenate all 50 columns in one string, then look if this string is LIKE whatever. I hope the table is not too large however! ok, great, thanks! ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211162 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: find_string in db
If it's something that's going to be done regularly, I'd look into oracle's full text searches. (You're using oracle, right?) I am. If you want to be lazy I'd do select * from mytable in a cfquery, then dump myquery.columnlist, loop through that and and build your like statements, then use that to do the real query. I took the lazy approach and it worked quickly. I probably should have just had someone look through the data but I was trying to help. thanks as usual! ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211182 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: find_string in db
Daniel,
Included below is a stored procedure that you can use to search any or all
columns in all tables.
Once you have installed the SP, call it as follows:
exec spDBSearch @value='MySearchString'
You can also specify a specific column, table or datatype. Let me know if you
have any questions.
ALTER PROCEDURE sp_DBSearch (@value varchar(255),@col varchar(255) = '',@table
varchar(1024) = '', @datatype varchar(20) = '', @debug int = 0)
as
SET NOCOUNT ON
-- cursor vars
DECLARE @colName varchar(255),@tableName varchar(255),@dType varchar(50),@op
varchar(10),
@searchVal varchar(255),@query varchar(1024),@Where
varchar(1024),@From varchar(255),
@recordCount int
SELECT @op = '=',
@recordCount = 0
CREATE TABLE #x (RecordCount int)
DECLARE cur CURSOR FAST_FORWARD
FOR
SELECT c.name as colName
--,c.id
,o.name as tableName
--,o.type
,t.name as DataType
FROMsysColumns as c
left outer join sysObjects as o on (o.id = c.id)
left outer join sysTypes as t on c.xtype = t.xtype
WHERE (
c.Name like IsNull(nullIf(@Col,''),c.name)
)
and
o.name like IsNull(nullIf(@table,''),o.name)
AND
t.name like IsNull(nullIf(@datatype,''),t.name)
AND
o.type = 'u'
order by o.name,c.Name
OPEN cur
WHILE (1=1)
BEGIN
FETCH NEXT FROM cur INTO @colName,@tableName,@dType
IF (@@FETCH_STATUS 0)
BREAK
SET @searchVal = @value
if (CHARINDEX('%',@value) 0)
SELECT @op = 'LIKE',
@searchVal = + @value +
ELSE IF (CHARINDEX(',',@value) 0)
SELECT @op = 'IN',
@searchval = '(' + @value + ')'
ELSE
SELECT @op = '=',
@searchVal = + @value +
SELECT @From = ' FROM ' + @tableName,
@Where = ' WHERE ' + @colName + ' ' + @op + ' ' +
@searchval
SELECT @query = 'SELECT count(*) as RecordCount' + @from + @where
INSERT INTO #x
exec (@query)
SELECT @recordCount = (SELECT top 1 recordCount FROM #x)
truncate table #x
if (@debug 0)
print('Searching ' + @tableName + '.' + @colName + ' for ' +
@value + ' returned ' + cast(@recordCount as varchar(10)) + ' records')
if (@recordCount 0)
BEGIN
SELECT @query = 'SELECT ''' + @tableName + ''' as
searchTable,''' + @colName + ''' as searchCol,*' + @from + @where
--SELECT@query = 'SELECT ''' + @tableName + ''' as
searchTable,''' + @colName + ''' as searchCol,* FROM ' + @tableName + ' WHERE '
+ @colName + ' ' + @op + ' ' + @searchval
exec (@query)
END
END
CLOSE cur
DEALLOCATE cur
DROP TABLE #x
[EMAIL PROTECTED] 07/05/05 09:25AM
I have a table that has about 50 columns. I want to be able to
search ALL columns for the string ***.
I can loop through the columnList with a set of LIKE commands, but is
there a way to search all columns without making 50 LIKE commands?
--
Daniel Kessler
Department of Public and Community Health
University of Maryland
Suite 2387 Valley Drive
College Park, MD 20742-2611
301-405-2545 Phone
www.phi.umd.edu
~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211187
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: find_string in db
That's unlikely to work in Oracle ;-) On 7/6/05, Chris Terrebonne [EMAIL PROTECTED] wrote: Daniel, Included below is a stored procedure that you can use to search any or all columns in all tables. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211227 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
