DECLARE @fullpath VARCHAR(500) SET @fullpath = 'E:\Masters\MP3 DJ Masters\MP3 CD 320\Artist Albums\Sonique\Hear My Cry\01 Sonique - It Feels So Good.mp3' SELECT CHARINDEX('\', REVERSE(@fullpath)) AS [CharPosition] -- Final '\' character in the data ,LEFT(@fullpath,LEN(@fullpath) - CHARINDEX('\', REVERSE(@fullpath))) AS [Folder] ,RTRIM(RIGHT(@fullpath, CHARINDEX('\', REVERSE(@fullpath)) - 1)) AS [FileName]
Monday, 30 June 2014
TSQL : Finding the last occurrence of a character
Thursday, 26 June 2014
SQL : Numeric Data Search Script - Version 1
After investigating another unknown database, I have adapted my Data Search Script to search for values in numeric columns.
/* NUMERIC DATA SEARCH SCRIPT version 1 : YOU MUST RUN THIS IN GRID VIEW!!!*/ declare @columncount int declare @searchdata int -- set string to search for here set @searchdata = 200908 select @columncount = count(*) from information_schema.columns where data_type in ('int','bigint') and table_name not like 'sync%' and column_name <> 'order' and table_name in (select name from sys.sysobjects with (nolock) where type = 'u') select 'declare @resultslist varchar(max)' + char(10) + 'set @resultslist = ''''' union select 'if exists (select 1 from [' + table_schema + '].[' + table_name + '] with (nolock) where [' + column_name + '] = ' + CAST(@searchdata as VARCHAR(15)) + ' ) ' + char(10) + ' begin ' + char(10) + ' set @resultslist = @resultslist + char(10) + ''select ''''' + table_name + ''''' as [table_name], [' + column_name + '],* from ['+ table_schema + '].[' + table_name + '] with (nolock) where [' + column_name + '] = ' + CAST(@searchdata as VARCHAR(15)) + ''''+ char(10) + ' raiserror( ''' + table_schema + '.' + table_name + '/' +column_name + ''', 10, 1) WITH NOWAIT' + char(10) + ' end ' + char(10) + '' + char(10) + ' raiserror( ''' + cast(cast(cast(rank() OVER (ORDER BY table_schema, table_name, column_name) as float)/ @columncount *100 as decimal(10,2)) as varchar(10))+ '%%' + ''', 10, 1) WITH NOWAIT' + char(10) from information_schema.columns with (nolock) where data_type in ('int','bigint') and table_name not like 'sync%' and column_name <> 'order' and table_name in (select name from sys.sysobjects with (nolock) where type = 'u') union select 'print @resultslist' + char(10)
Subscribe to:
Posts (Atom)