www.muppix.co explore directories [begin end last days minutes size greater]v

SELECT * FROM master.dbo.sysdatabases	names & sizes of all connected hard-drives on this version of linux. TIP: goto using these harddrive names
SELECT TABLE_NAME ,TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME	select all subdirectory (mydir) names
DECLARE @SQL NVARCHAR(MAX)= '';SELECT @SQL = @SQL +'SELECT ''' +t1.name + ''' AS DatabaseName,t.name AS TableName, t2.name AS ColumnName, t.modify_date FROM ' +t1.name +'.sys.tables t INNER JOIN ' +t1.name + '.sys.columns t2 ON t2.object_id = t.object_id WHERE t.name LIKE ''%mytable%'' UNION ALL ' FROM sys.databases t1 WHERE t1.name IN('mydatabase') ; SET @SQL =left(@SQL, LEN(@SQL) - 10); EXEC sp_executesql @SQL	select every directory & subdirectory with 'mydir' somewhere (in between ) in its name , include the times last saved and access, ignore case
SELECT TABLE_SCHEMA, TABLE_NAME ,TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE '%mytable%' ORDER BY TABLE_NAME	select every directory & subdirectory with 'mydir' somewhere (in between ) in its name , include the times last saved and access, ignore case
DECLARE @SQL NVARCHAR(MAX)= '';SELECT @SQL = @SQL +'SELECT ''' +t1.name + ''' AS DatabaseName,t.name AS TableName, t2.name AS ColumnName, t.modify_date FROM ' +t1.name +'.sys.tables t INNER JOIN ' +t1.name + '.sys.columns t2 ON t2.object_id = t.object_id WHERE t2.name LIKE ''%mycolumn%'' UNION ALL ' FROM sys.databases t1 WHERE t1.name IN('mydatabase'); SET @SQL =left(@SQL, LEN(@SQL) - 10); EXEC sp_executesql @SQL	select all databases & tables / directory & subdirectory with 'mycolumn' somewhere (in between ) in its column name (ignore case)
DECLARE @SQL NVARCHAR(max) = '';SELECT @SQL = @SQL + 'select ''' + name + ''' AS DBname, * FROM [' + name + '].sys.tables union all ' FROM sys.databases; SET @SQL = STUFF(@SQL, len(@SQL) - 9, 11, '');EXEC sp_executesql @SQL -- oct2022	select all databases, tables / directory & subdirectory , all columns
SELECT TABLE_SCHEMA,TABLE_NAME ,TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'mytable%' ORDER BY TABLE_NAME	select every directory & subdirectory beginning with 'mydir' , include the saved times and access
SELECT TABLE_NAME ,TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME	select all hidden subdirectory names
EXEC sp_MSForEachTable 'exec sp_spaceused [?]'	sort by size each directory (mydir) and subdirectory ie: you've run out of space & need to delete stuff (try doing that in Windows..)
DECLARE @SQL NVARCHAR(MAX) = '';SELECT @SQL =@SQL + 'SELECT ''' + t1.name + ''' AS DatabaseName, t.name AS TableName, t2.name AS ColumnName, t.modify_date FROM ' + t1.name + '.sys.tables t INNER JOIN ' + t1.name + '.sys.columns t2 ON t2.object_id = t.object_id WHERE t2.name LIKE ''%mycolumn%'' AND t.modify_date >DATEADD(day, -200, GETDATE()) UNION ALL ' FROM sys.databases t1 WHERE t1.name IN('mydatabase');SET @SQL =left(@SQL, LEN(@SQL) - 10);EXEC sp_executesql @SQL	select directory and subdirectory (mydir) with files saved in last 2 days, include size, saved dates
SELECT * FROM sys.tables WHERE modify_date >= DATEADD(day, -2, GETDATE()) ORDER BY modify_date DESC	select directory and subdirectory (mydir) with files saved in last 2 days, include size, saved dates
SELECT t1.name AS mycolumns,t2.name AS mytables ,t2.modify_date FROM sys.columns t1 JOIN sys.tables t2 ON t1.object_id = t2.object_id WHERE t2.Modify_date >= DATEADD(day, -2, GETDATE()) ORDER BY mytables,mycolumns, t2.modify_date	select directory created in last 2 days, (begin character on column 3 is "d") include size, saved dates

www.muppix.co explore filenames [begin end filename hidden myextension last days minutes size greater 2K 2M]

SELECT * FROM mydatabase.INFORMATION_SCHEMA.COLUMNS	select all filenames (include size/date information) in subdirectory. TIP: to just show the filesnames, also add this at end: | cut -d'.' -f2-
SELECT * FROM [BXT00001\mydir].[mydatabase].[dbo].[mytable] -- from another server	selects all files names from all subdirectory
SET NOCOUNT ON;DECLARE @mytmp TABLE (tablename VARCHAR(255), rowcounts BIGINT, reserved VARCHAR(255), data_size VARCHAR(255), index_size VARCHAR(255), unused VARCHAR(255)); DECLARE @cmd1 VARCHAR(255);SET @cmd1 = 'exec sp_spaceused ''?'''; INSERT INTO @mytmp (tablename,rowcounts,reserved,data_size,index_size,unused) EXEC sp_msforeachtable @command1=@cmd1; SELECT * FROM @mytmp ORDER BY CONVERT(BIGINT,REPLACE(data_size,'KB','')) DESC	select files in subdirectory, sorted by size
SELECT s.name AS ColumnName,t.name AS TableName FROM sys.columns s JOIN sys.tables t ON s.object_id = t.object_id WHERE s.name LIKE '%mycolumn%'	select filenames with '' in between/somewhere in the filename (include size/date & subdirectory/path information) in all sub directory. ignore case
SET NOCOUNT ON;DECLARE @mytmp TABLE (tablename VARCHAR(255), rowcounts BIGINT, reserved VARCHAR(255), data_size VARCHAR(255), index_size VARCHAR(255), unused VARCHAR(255)); DECLARE @cmd1 VARCHAR(255);SET @cmd1 = 'exec sp_spaceused ''?'''; INSERT INTO @mytmp (tablename,rowcounts,reserved,data_size,index_size,unused) EXEC sp_msforeachtable @command1=@cmd1; SELECT * FROM @mytmp WHERE CONVERT(BIGINT,REPLACE(data_size,'KB','')) BETWEEN 2 AND 2000	select files in all subdirectory , size between 2K & 2Mb. Each k is 1024 bytes, include sizes, saved date, path
SELECT t1.name AS mytables ,t2.name AS mycolumns, t1.modify_Date AS Modified FROM sys.columns t2 JOIN sys.tables t1 ON t2.object_id = t1.object_id WHERE t1.Modify_date >= DATEADD(day, -2,GETDATE()) ORDER BY mytables,mycolumns, t1.modify_Date	files in subdirectory saved in last 2 days

select lines with 'mytext' in files [filename begin end ignore case number aswell mysecondtext]

SELECT * FROM mytable WHERE mycolumn IN ('mytext', 'mysecondtext','mythirdtext')	select lines with 'mytext' or 'mysecondtext' or 'mythirdtext', ignore case, in every directory
SELECT t1.*,t2.* FROM mytable t1 JOIN mysecondtable t2 ON t1.mycolumn=t2.mycolumn	select lines from a list of text/words in the file mylist.txt, (mytext or mysecondtext or thirdtext etc) all subdirectory from here on, ignore case TIP:on a Windows PC, ensure you run dos2unix dos2unix on mylist.txt!
	import CSV into mytable

select line with 'mytext' [begin end before after aswell or mysecondtext mythirdtext word ignore case]

SELECT mycolumn FROM mytable WHERE mycolumn LIKE '%mytext%'	select if 'mytext' is anywhere on the line
SELECT * FROM mytable WHERE mycolumn='mytext'	select lines with 'mytext' ignore case. ie: could match MytEXT mytext or MYTEXT etc
SELECT * FROM mytable WHERE (mycolumn LIKE '%mytext%') AND (mycolumn LIKE '%mysecondtext%')	select line with both 'mytext' aswell as 'mysecondtext' in any order on the line
SELECT * FROM mytable WHERE (LOWER(mycolumn) =LOWER('mytext')) AND (LOWER(mysecondcolumn) = LOWER( 'mysecondtext'))	select line with 'mytext' aswell as 'mysecondtext' on the line (ignore case)
SELECT * FROM mytable WHERE (mycolumn = 'mytext') AND (mycolumn = 'mysecondtext') AND (mycolumn = 'mythirdtext')	select line with 'mytext' aswell as 'mysecondtext' aswell as 'mythirdtext' in any order (ignore case)
SELECT * FROM mytable WHERE 0 < COALESCE( NULLIF( PATINDEX( '%mytext%', mycolumn),0), NULLIF( PATINDEX( '%mysecondtext%' ,mycolumn) ,0))	select either 'mytext' or 'mysecondtext'
SELECT * FROM mytable WHERE 0 < COALESCE( NULLIF( PATINDEX( '%mytext%' ,mycolumn),0) ,NULLIF( PATINDEX( '%mysecondtext%' ,mycolumn),0) ,PATINDEX( '%mythirdtext%' ,mycolumn))	select line with 'mytext' or 'mysecondtext' or 'mythirdtext', ignore case
SELECT * FROM mytable WHERE 0 < COALESCE( NULLIF( PATINDEX( '%mytext%' ,mycolumn),0) ,NULLIF( PATINDEX( '%mysecondtext%' ,mycolumn),0) ,PATINDEX( '%mythirdtext%' ,mycolumn))	select lines with 'mytext' or 'mysecondtext' or 'mythirdtext'
SELECT t1.*,t2.* FROM mytable t1 JOIN mysecondtable t2 ON t1.mycolumn=t2.mycolumn	select any of the texts in the file mylist.txt 'mytext' or 'mysecondtext' or 'mythirdtext' etc TIP: in Windows ensure you run dos2unix on mylist.txt, so Linux can read it
SELECT * FROM mytable WHERE mycolumn LIKE 'mytext%'	select line that begin with 'mytext' TIP:may first want to ensure there are no leading spaces
SELECT * FROM mytable WHERE mycolumn LIKE 'mytext[A-D]%'	select line that begin with (range) 'mytextA' or 'mytextB' or 'mytextC' or 'mytextD'
SELECT * FROM mytable WHERE mycolumn LIKE '%mytext'	select line ending with 'mytext'
	select line ending with 'mytext'
	select all punctuation characters (punctuation chars)
SELECT * FROM mytable WHERE mycolumn LIKE '[A-D]%'	select line that begin with character 'A','B','C' or 'D' (range)
SELECT * FROM mytable WHERE mycolumn NOT LIKE '[A-D]%'	delete line that begin with character 'A','B','C' or 'D' (range)
SELECT mycolumn FROM mytable WHERE LEFT( mycolumn, CHARINDEX( 'mysecondtext' , mycolumn)) LIKE '%mytext%'	select line where 'mytext' is before 'mysecondtext', 'mysecondtext' after 'mytext'
SELECT * FROM mytable WHERE LEN(REPLACE( mycolumn, 'mytext','?'))- LEN(REPLACE( mycolumn, 'text',''))>=2	select line where 'mytext' appears twice or more often - second occurrence
SELECT * FROM mytable WHERE (mycolumn+' ') LIKE '%mytext %'	select line with word/column ending in 'mytext'. ie: find words/columns ending in 'ion' 'ing'
SELECT * FROM mytable WHERE SUBSTRING( mycolumn, LEN( 'mytext')+ CHARINDEX( 'mytext' , mycolumn) ,9999) LIKE '%mytext%'	select line if 'mytext' appears atleast twice duplicated - also for a second time on each line

select a section of lines [lines above below mytext after blankline between mysecondtext]

SELECT TOP (SELECT TOP 1 myrow FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS myrow,* FROM mytable) X WHERE mycolumn LIKE '%mytext%') * FROM mytable	select beginning lines above 'mytext', delete lines below 'mytext'
SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS myrow FROM mytable) Y WHERE myrow>(SELECT TOP 1 myrow FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS myrow FROM mytable) X WHERE mycolumn LIKE '%mytext%')	select lines below 'mytext' to end of file, including 'mytext'. delete beginning lines above 'mytext'
SELECT * FROM (SELECT Row_Number() OVER (ORDER BY (SELECT 1)) AS myrow,* FROM mytable) AS t1 WHERE t1.myrow BETWEEN 2 AND 20	select section lines between second (fixed) line to line 20 (fixed) ie: cat -n |awk 'NR>=2&&NR<=20'
SELECT * FROM (SELECT Row_Number() OVER (ORDER BY (SELECT 1)) AS myrow, * FROM mytable) AS t1 WHERE t1.myrow BETWEEN (SELECT TOP 1 myrow FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS myrow FROM mytable) X WHERE mycolumn LIKE '%mytext%') AND (SELECT TOP 1 myrow FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS myrow FROM mytable) X WHERE mycolumn LIKE '%mysecondtext%')	select section of lines between / below 'mytext' and 'mysecondtext'

delete lines [begin end above below duplicate blanklines]

TRUNCATE TABLE mytable	empty-out entire contents/delete all lines in .txt
IF OBJECT_ID('mytable', 'U') IS NOT NULL DROP TABLE mytable	empty-out entire contents/delete mytable
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY mycolumn) AS myrow, * FROM mytable ) X WHERE myrow >1	delete just the beginning (fixed) line, select below beginning line
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY mycolumn) AS myrow, * FROM mytable ) X WHERE myrow < (SELECT COUNT(*) FROM mytable)-1	delete just the end (fixed) line, select lines above
SELECT * FROM mytable WHERE NOT mycolumn LIKE '%mytext%'	delete line if 'mytext' is somewhere on the line (ignore case) TIP: first dble check which line will be deleted by running: fgrep -i 'mytext'
SELECT * FROM mytable WHERE NOT mycolumn LIKE 'mytext%'	delete lines that begin with 'mytext'
SELECT * FROM mytable WHERE NOT mycolumn LIKE '%mytext'	delete lines that end with 'mytext'
DELETE FROM mytable WHERE mycolumn = 'mytext' OR mycolumn= 'mysecondtext'	delete lines with 'mytext' or 'mysecondtext'
SELECT * FROM mytable WHERE NOT mycolumn ='mytext' OR mycolumn ='mysecondtext'	delete line with 'mytext' or 'mysecondtext' anywhere on the line (ignore case)
SELECT t1.*,t2.* FROM mytable t1 FULL OUTER JOIN mysecondtable t2 ON t1.mycolumn =t2.mycolumn WHERE t1.mycolumn IS NULL	delete lines if any of the texts in the file mylist.txt are found, 'mytext' etc TIP: in Windows ensure you run dos2unix on mylist.txt
SELECT * FROM mytable WHERE NOT (mycolumn ='mytext' AND mysecondcolumn ='mysecondtext')	delete line with 'mytext' aswell 'mysecondtext' anywhere on the line
SELECT mycolumn FROM mytable WHERE NOT LEFT( mycolumn, CHARINDEX( 'mysecondtext' , mycolumn)) LIKE '%mytext%'	delete lines with 'mytext' before 'mysecondtext' ('mysecondtext' after 'mytext'
SELECT * FROM mytable WHERE LTRIM(RTRIM(ISNULL(mycolumn,'')))<>''	truly delete blanklines which may have some spaces or tabs or no spaces at all
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY mycolumn,mysecondcolumn ,mythirdcolumn ORDER BY mycolumn ,mysecondcolumn ,mythirdcolumn) AS myrowno FROM mytable )X WHERE myrowno =1	sort & delete duplicate lines (dont maintain the original order & is a lot faster)
;WITH mytmp AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY mycolumn ,mysecondcolumn ,mythirdcolumn ORDER BY mycolumn ,mysecondcolumn ,mythirdcolumn ASC) AS myrow FROM mytable) SELECT * FROM mytmp WHERE myrow=1	sort & delete duplicate lines (dont maintain the original order & is a lot faster)
SELECT * FROM ( SELECT *, COUNT(*) OVER (PARTITION BY mycolumn,mysecondcolumn ) AS mynewcol FROM mytable ) X WHERE mynewcol >1	select only the duplicate lines, ie: those lines that occur twice or more
;WITH mytmp AS (SELECT *,COUNT(*) OVER (PARTITION BY mycolumn ,mysecondcolumn ) AS myrow FROM mytable) SELECT * FROM mytmp WHERE myrow>1	select only the duplicate lines, ie: those lines that occur twice or more
SELECT mycolumn,mysecondcolumn ,COUNT(*) FROM mytable GROUP BY mycolumn, mysecondcolumn HAVING COUNT(*)>1 -- but limited to these flds only	select only the duplicate lines, ie: those lines that occur twice or more
SELECT * FROM mytable WHERE (mycolumn IN (SELECT mycolumn FROM mytable GROUP BY mycolumn HAVING COUNT(*)>1 )) -- select all colums	select only the duplicate lines, ie: those lines that occur twice or more
SELECT mycolumn ,mysecondcolumn ,mythirdcolumn FROM mytable GROUP BY mycolumn ,mysecondcolumn ,mythirdcolumn	delete duplicate lines, but maintain the original order ( without sorting) select begin occurrence of each line
SELECT mysecondcolumn FROM mytable GROUP BY mysecondcolumn	delete duplicate lines, based on duplicates in second column only, select begin occurrence of 2nd column,preserve order of lines
SELECT * FROM(SELECT ROW_NUMBER() OVER (ORDER BY mycolumn) AS myrow,* FROM mytable) X WHERE myrow > 2	delete the (fixed) beginning and second lines, select lines below second line, to the end line
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY mycolumn) AS myrow, * FROM mytable ) X WHERE myrow NOT BETWEEN 2 AND 8	delete between second line to eigth line : (fixed) lines 2 3 4 5 6 7 8
SELECT * FROM(SELECT ROW_NUMBER() OVER (ORDER BY mycolumn) AS myrow,* FROM mytable) X WHERE myrow <(SELECT COUNT(*)-2 FROM mytable)	delete second (fixed) lines from end
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS myrow,* FROM mytable) X WHERE myrow <(SELECT COUNT(*)-3 FROM mytable)	delete the end (fixed) 3 lines, including second line, select lines above the end 3 lines
SELECT mycolumn,mysecondcolumn, COUNT(*) FROM mytable GROUP BY mycolumn ,mysecondcolumn HAVING COUNT(*) >1	how many/occurrence of duplicate lines - pivot table

delete 'mytext' in the line [begin end before after between number second mychar mydelimiter word occurrence]

SELECT REPLACE(mycolumn,'mytext','') FROM mytable	delete 'mytext' on the line if found
ALTER TABLE mytable DROP COLUMN mysecondcolumn -- may need to ALTER TABLE mytable DROP CONTRAINT [DF__mytable__mycol__1 (fill this in !)]	delete second column / word (delimiter is spaces by default)
SELECT LEFT( mycolumn, CHARINDEX( 'mytext', mycolumn + 'mytext')-1 ) FROM mytable	select everything before 'mytext' on the line, (delete 'mytext' & everything after)
SELECT mycolumn, SUBSTRING( mycolumn, CHARINDEX('mytext',mycolumn) ,9999 ) FROM mytable	delete everything before 'mytext' on the line, (select text after 'mytext')
SELECT REVERSE( SUBSTRING( REVERSE( ' '+ mycolumn) ,CHARINDEX( ' ',REVERSE( ' '+ mycolumn)) ,999)) FROM mytable	delete end word / end column
SELECT REVERSE( SUBSTRING( REVERSE( ',' +mycolumn) ,CHARINDEX( ',', REVERSE( ',' +mycolumn)) ,999)) FROM mytable	delete end word / end column with comma ',' as mydelimiter
SELECT SUBSTRING(mycolumn, 2+1,999) FROM mytable	delete beginning and second characters (fixed)(delete before 3rd & select after second characters)
SELECT IIF(mycolumn='','',LEFT(mycolumn,LEN(mycolumn)-1)) FROM mytable	delete end character (fixed) on each line
SELECT LEFT( mycolumn,CASE WHEN 0 > LEN( mycolumn) -2 THEN 0 ELSE LEN( mycolumn) -2 END) FROM mytable	delete the end 2 (fixed) characters on each line. (end & second from end character)
SELECT SUBSTRING( mycolumn ,PATINDEX( '% %' ,mycolumn +' ') ,999) FROM mytable	delete beginning word / column
SELECT LTRIM(mycolumn) FROM mytable	left align /justify, delete beginning/leading spaces and or tabs on each line
SELECT REPLACE(LTRIM(REPLACE(mycolumn ,'0',' ')),' ','0') FROM mytable	delete begin / leading zero/ mychar
SELECT REPLACE(RTRIM(REPLACE(mycolumn ,'0',' ')),' ','0') FROM mytable	delete ending / trailing zero/ mychar
SELECT RTRIM(mycolumn) FROM mytable	delete spaces or tabs at end of each line. right align. also deletes extra spaces on blanklines
SELECT LTRIM(RTRIM(mycolumn)) FROM mytable	delete leading/beginning space aswell as ending/trailing spaces on the line(left align, trim )
SELECT *, SUBSTRING(LEFT( mycolumn, CHARINDEX('mysecondtext',mycolumn)-1),LEN('mytext') + CHARINDEX('mytext', mycolumn),999) AS mycol FROM mytable WHERE mycolumn LIKE '%mytext%' AND mycolumn LIKE '%mysecondtext%'	select text between 'mytext' and 'mysecondtext' on the line. delete before 'mytext' aswell as after 'mysecondtext', include mytext & mysecondtext
SELECT *,LEFT(mycolumn,CHARINDEX('mytext',mycolumn)-1) + SUBSTRING(mycolumn,LEN('mysecondtext')+CHARINDEX('mysecondtext',mycolumn),999) AS mycol FROM mytable WHERE mycolumn LIKE '%mytext%' AND mycolumn LIKE '%mysecondtext%'	delete the text between 'mytext' and 'mysecondtext'
SELECT CASE WHEN 0= COALESCE ( NULLIF( PATINDEX( '%mytext%' ,mycolumn ),0),0) THEN mycolumn ELSE '' END FROM mytable	delete words/columns anywhere on the line, with 'mytext' somewhere inside/between the word ie: will delete words such as 'allmytext' or 'mytexting' or 'mytext'
SELECT RTRIM(LTRIM( REPLACE(REPLACE(REPLACE( mycolumn ,' ','<>'),'><',''),'<>',' '))) FROM mytable	delete/ replace multiple/duplicate/consecutive spaces with single space/blank
	delete/ replace multiple/duplicate/consecutive spaces with single space/blank
SELECT REPLACE( REPLACE( REPLACE( mycolumn,'a', '') ,'b','') ,'c' ,'') FROM mytable	delete all occurrence of any of these 3 single (mychar) character 'a','b' or 'c' (ie: also delete 'abc', dan' etc) delete multiple/duplicate characters
DECLARE @mytabl TABLE (mycol varchar(50), myrow int, mystring varchar(MAX)) INSERT INTO @mytabl (mycol, myrow,mystring) SELECT t2.mycolumn AS mycol ,IIF(iter.pos=1,1,0) AS myrow ,IIF(ASCII(SUBSTRING(' '+t2.mycolumn+'|' ,iter.pos,1)) NOT BETWEEN 97 AND 122 ,SUBSTRING(' '+t2.mycolumn+ '|' ,iter.pos,1),' ') mystring FROM mytable t2,(SELECT id AS pos FROM T100) iter WHERE iter.pos <= 1+ LEN(t2.mycolumn) SELECT LEFT(STUFF((SELECT '' +[mystring] FROM @mytabl WHERE (mycol = t1.mycol) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') ,LEN(mycol)+1) AS mystrings ,t3.* FROM @mytabl t1 JOIN mytable t3 ON t3.mycolumn=t1.mycol WHERE myrow=1	delete all lowercase characters on a line
SELECT mycolumn FROM ( SELECT mytable.mycolumn, iter.pos, SUBSTRING(mytable.mycolumn,iter.pos,1) C, ASCII(SUBSTRING(mytable.mycolumn,iter.pos,1)) val FROM mytable, (SELECT id AS pos FROM T10 ) iter WHERE iter.pos <= LEN(mytable.mycolumn)) x GROUP BY mycolumn HAVING MIN(val) BETWEEN 48 AND 122	delete non-printable punctuation characters
GO CREATE FUNCTION [dbo].[fnStripChars](@Res NVARCHAR(MAX), @Patrn VARCHAR(255)) RETURNS NVARCHAR(MAX)AS BEGIN SET @Patrn = '%['+@Patrn+']%' WHILE PatIndex(@Patrn, @Res)>0 SET @Res = Stuff(@Res, PatIndex(@Patrn, @Res), 1, '') RETURN @Res END GO SELECT dbo.fnStripChars(mycolumn, '^a-z0-9') FROM mytable	replace punctuation with space (delete all punctuation)
SELECT REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( mycolumn ,'.',' ' ),'=','' ),'$',' '),'(',' ' ),')',' ') FROM mytable	replace punctuation with space (delete all punctuation)
SELECT LEFT(SUBSTRING( mycolumn, PATINDEX('%[0-9.]%',mycolumn), LEN(mycolumn)) +'z', PATINDEX('%[^0-9.]%', SUBSTRING( mycolumn, PATINDEX('%[0-9.]%', mycolumn), LEN(mycolumn))+'z' )-1) FROM mytable	delete all text/characters, select only numbers
DECLARE @mytabl TABLE (mycol varchar(50), myrow int, mynum varchar(MAX)) INSERT INTO @mytabl (mycol,myrow,mynum) SELECT t2.mycolumn AS mycol ,IIF(iter.pos=1,1,0) AS myrow ,IIF(SUBSTRING(' '+t2.mycolumn+'|' ,iter.pos,1) BETWEEN '0' AND '9' ,SUBSTRING(' '+t2.mycolumn+ '|' ,iter.pos,1),' ') mynum FROM mytable t2,(SELECT id AS pos FROM T100) iter WHERE iter.pos <= 1+ LEN(t2.mycolumn) SELECT LEFT(STUFF((SELECT '' +[mynum] FROM @mytabl WHERE (mycol = t1.mycol) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') ,LEN(mycol)+1) AS mynums ,t3.* FROM @mytabl t1 JOIN mytable t3 ON t3.mycolumn=t1.mycol WHERE myrow=1	delete all text/characters, select numbers
DECLARE @mytabl TABLE (mycol varchar(50), begins int, mystring varchar(MAX)) INSERT INTO @mytabl (mycol, begins,mystring) SELECT t2.mycolumn AS mycol ,IIF(iter.pos=1,1,0) AS begins ,IIF(SUBSTRING(' '+t2.mycolumn+'|' ,iter.pos,1) NOT BETWEEN '0' AND '9' ,SUBSTRING(' '+t2.mycolumn+ '|' ,iter.pos,1),' ') mystring FROM mytable t2,(SELECT id AS pos FROM T100) iter WHERE iter.pos <= 1+ LEN(t2.mycolumn) SELECT mycol ,LEFT(STUFF((SELECT '' +[mystring] FROM @mytabl WHERE (mycol = t1.mycol) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') ,LEN(mycol)+1) AS mystrings FROM @mytabl t1 WHERE begins=1	delete all numbers, select all characters TIP: see section on [:digit:] for other groups of characters
SELECT dbo.fnStripChars(mycolumn, '^0-9') FROM mytable	delete all text/characters, select only numbers
	delete all text/characters, select only numbers
SELECT (CASE WHEN mycolumn NOT LIKE '%[^0-9]%' THEN mycolumn END)AS mynum1 ,TRY_PARSE(mycolumn AS INT) AS mynum2 ,IIF(1=ISNUMERIC(mycolumn),mycolumn,NULL) AS mynum3 FROM mytable	delete all text/characters, select numbers

www.muppix.co select / delete columns [ mytext begin end second or delete mychar mydelimiter split]

SELECT SUBSTRING( mycolumn,0 ,CHARINDEX( ' ',mycolumn+' ')) FROM mytable	select beginning column only
SELECT COALESCE( mycolumn, mysecondcolumn ,mythirdcolumn) FROM mytable	select beginning word/column from 3 columns
SELECT mycolumn,REPLACE(X.i.value('.','VARCHAR(50)'),'|','') AS mydata FROM ( SELECT mycolumn, CONVERT(XML,''+ REPLACE(mycolumn+' | | | | | | | | |' ,' ','')+'') AS mydata FROM mytable )X1 CROSS APPLY mydata.nodes('i[position()= 2 ]') AS X(i)	select second column
SELECT LTRIM( RIGHT( mycolumn, CHARINDEX(' ', REVERSE( ' ' +mycolumn)))) FROM mytable	select only the end column, delete all columns before the end column
SELECT mycolumn,myendcolumn FROM mytable	select second column and end column
SELECT LEFT(mycolumn,2-1)+SUBSTRING ( mycolumn ,1+8,999), * FROM mytable	delete between second (fixed) character and 8th character
SELECT * FROM mytable WHERE LEFT( mycolumn ,LEN( 'mytext')) = 'mytext'	select line if begin column is 'mytext'
SELECT * FROM mytable WHERE RIGHT(mycolumn,LEN('mytext')) = 'mytext'	select line if end column is 'mytext'
	select line if end column is 'mytext'
SELECT * FROM mytable WHERE mysecondcolumn = 'mytext'	select line if second column is 'mytext'
SELECT * FROM mytable WHERE mysecondcolumn = 'mytext'	select line if second column is 'mytext', but column mydelimiter is '|'
SELECT * FROM mytable WHERE mysecondcolumn LIKE '%mytext%' OR mysecondcolumn LIKE '%mysecondtext%' OR mysecondcolumn LIKE '%mythirdtext%'	select whole line if 'mytext' or 'mysecondtext' or 'mythirdtext' is somewhere in the second column (wildcard)
SELECT * FROM mytable WHERE mysecondcolumn LIKE 'mytext%'	select line if second column begins with 'mytext'
SELECT * FROM mytable WHERE mysecondcolumn LIKE '%mytext'	select line if second column ends with 'mytext'
SELECT mycolumn ,mysecondcolumn ,mythirdcolumn FROM mytable WHERE (mysecondcolumn = 'mytext')	select column 1,2,3,4 if second column is 'mytext'
SELECT * FROM mytable WHERE SUBSTRING(mycolumn,2,6)='mytext'	select line if (fixed) character columns 2-7 is 'mytext' (from second character, for 6 characters , as length of mytext is 6 )
DELETE * FROM mytable WHERE mysecondcolumn = 'mytext'	delete line if second column is 'mytext'
SELECT * FROM mytable WHERE mysecondcolumn NOT LIKE '[a-zA-Z0-9]	delete lines if second column not alphanumeric (range)
SELECT * FROM mytable WHERE mysecondcolumn LIKE '[A-Z]%'	select line if second column begins with uppercase (range) character (mychar)
SELECT * FROM mytable WHERE mysecondcolumn LIKE '[A-Z]'	select line if the entire second column is all uppercase
SELECT * FROM mytable WHERE mysecondcolumn LIKE '[0-9]%'	select line if second column begins with 'mytext' or begins with a number [0-9]
SELECT * FROM mytable WHERE NOT mysecondcolumn LIKE 'mytext%'	delete line if second column begins with 'mytext'
SELECT * FROM mytable WHERE LEN(CAST(mysecondcolumn AS VARCHAR))>2	if length of second column is greater 2 (or less) , select the line
SELECT * FROM mytable WHERE mycolumn LIKE '__%'	if length of mycolumn is more than 2 select line
SELECT * FROM mytable WHERE ISNULL(mysecondcolumn,'')=''	if length of second column is 0 / empty ( or less ) , select the line
SELECT myeigthcolumn,mycolumn,mysecondcolumn FROM mytable WHERE mysecondcolumn LIKE '%mytext'	select if 'mytext' is at the end of second column and select eigth , beginning aswell second columns
SELECT * FROM mytable WHERE NOT SUBSTRING(mycolumn,2,6)='mytext'	delete lines if (fixed) character columns 2-7 is 'mytext' (from second character, for 6 characters , as length of mytext is 6 )
SELECT IIF(LEFT(mycolumn, LEN('mytext')) = 'mytext' , SUBSTRING(mycolumn ,1+LEN('mytext'),999) ,mycolumn ),* from mytable	delete 'mytext' if it is at the beginning of the line
SELECT IIF(RIGHT(mycolumn, LEN('mytext')) = 'mytext' , LEFT(mycolumn ,LEN(mycolumn)-LEN('mytext')) ,mycolumn) ,* from mytable	delete 'mytext' if it is at the end of line
SELECT * FROM mytable WHERE RIGHT(mycolumn, LEN('mytext')) <> 'mytext'	delete the whole line if 'mytext' is at the end of line
SELECT TOP 2 * FROM mytable ORDER BY mycolumn ASC	select the beginning (fixed) begin and second lines (above), delete lines below second line
SELECT TOP 2 * FROM mytable ORDER BY mycolumn DESC	select (fixed) end line and second from end line , delete beginning/above lines. ie: tail -100 , end 100 lines TIP:useful for selecting mytext on live log files
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY mycolumn) AS myrow, * FROM mytable ) X WHERE NOT myrow >=2	select the second (fixed) lines & below , delete lines above second line
SELECT TOP 88 * FROM (SELECT TOP (88+2) * FROM mytable ORDER BY mycolumn ASC)X ORDER BY mycolumn DESC	select fixed line, between second line to 88th line,delete before 2, after 88 ,useful in splitting up a file

research: select lines with 'mytext' and also lines above or below

SELECT * FROM mytable WHERE LEN(CAST(mycolumn AS VARCHAR))>2	select line greater than (fixed) 2 characters length (second) , delete lines smaller than 1 2 ( < less than)
SELECT TOP 1 * FROM mytable ORDER BY LEN(CAST(mycolumn AS VARCHAR)) DESC	select the longest line

numbers or values [greater smaller equals number end begin second column delete]

SELECT * FROM mytable WHERE mycolumn LIKE '%[0-9]%'	select lines with a number (range) somewhere on the line
	select lines with a number / digit (range) somewhere on the line
SELECT * FROM mytable WHERE NOT mycolumn LIKE '%[0-9]%'	delete lines with a number (range) somewhere on the line
	if a number on the line is greater than 2.0 ,select whole line. range TIP: number must be 1234 & not 1,234, must have space infront, no negative numbers & may need need to delete commas first and replace () for negative numbers
SELECT * FROM mytable WHERE mycolumn > 2.0	select line if begin column has a number/value : is greater than 2.0
	select lines with 2 consecutive numbers
	lines with atleast 2 consecutive numbers/digits, or more (length)
GO CREATE FUNCTION [dbo].[fnStripNum] (@Txt varchar(100)) RETURNS varchar(100) AS BEGIN DECLARE @Res varchar(100) SET @Res = '' IF @Txt LIKE '%[0-9]%' BEGIN SELECT @Res = @Res + SUBSTRING( @Txt,N.ID,1) FROM dbo.T100 N WHERE N.ID <= LEN(@Txt) AND SUBSTRING( @Txt ,N.ID,1) LIKE '[^0-9]' END ELSE BEGIN SET @Res = @Txt END RETURN @Res END GO SELECT dbo.fnStripNum(mycolumn) from mytable --use table T100. create function only once!	delete all numbers on the line (range of characters 0-9)
SELECT CAST( LEFT( mycolumn +'A', PATINDEX( '%[^0-9]%' ,mycolumn +'A')- 1) AS BIGINT) ,mycolumn FROM mytable	select numbers before characters , delete characters after the numbers
	select text after numbers on line
SELECT mycolumn FROM mytable WHERE LEN(dbo.fnStripChars(mycolumn, '^0-9'))=LEN(mycolumn)	delete lines with just numbers (lines beginning with just single integer amount) (can select the range/character set other than numeric characters)
SELECT REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( mycolumn,'0','') ,'1', ''), '2', ''), '3', ''), '4','') ,'5' ,'') ,'6',''),'7',''),'8',''),'9','') FROM mytable	delete all numbers/digits
	delete all numbers/digits
SELECT * FROM mytable WHERE mysecondcolumn = 2.0	if second column has a number/value : is exactly equals to 2.0 ie: column could select 10.000, 10 or 010, select whole line
SELECT (CONVERT( decimal(15,2) ,REPLACE( REPLACE( REPLACE( mycolumn,'(','-') ,')','') ,',' ,''))) FROM mytable	convert/clean up numbers in second column so can be summed up,ie negative number (123,456)
	atleast 2 consecutive numbers/digits ie: 09 or 10 or 9999 (but not 2.5)
SELECT * FROM mytable WHERE mysecondcolumn BETWEEN 2 AND 10.1	select lines if second column number /value is between 2.0 - 10.1 , it is greater than 2.0 and less than 10.1
SELECT dbo.fnStripChars(mycolumn, '0-9a-zA-Z') FROM mytable	delete all characters aswell as letters/numbers on a line , select just the punctuation characters
SELECT * ,mysecondcolumn + mythirdcolumn AS mytotal FROM mytable	select text & insert end column - sum up/ add second & third columns
SELECT SUM(mycolumn) FROM mytable	sum up a single column/ list of numbers ie
SELECT mycolumn,mysecondcolumn,mysum FROM (SELECT t1.mycolumn, t1.mysecondcolumn, SUM(t2.mysecondcolumn) AS mysum FROM mytable t1 INNER JOIN mytable t2 ON t1.mycolumn >= t2.mycolumn GROUP BY t1.mycolumn, t1.mysecondcolumn) X ORDER BY mysum	cumulative sum up of second column
SELECT SUM(mysecondcolumn) FROM mytable	sum second column
SELECT AVG(mysecondcolumn) FROM mytable	average of second column
SELECT MIN(mysecondcolumn) FROM mytable	line with minimum number in second column
SELECT MAX(mysecondcolumn) FROM mytable	maximum of second column
SELECT mycolumn,ROUND( mysecondcolumn,2) ,mythirdcolumn FROM mytable	round second column to 2 decimal places

replace or convert text [mysecondtext beginning ignore case mythirdtext begin end line mychar duplicate space list]

UPDATE mytable SET mysecondcolumn = 'mynewtext'WHERE mycolumn = 'mytext'	replace every 'mytext' with 'mysecondtext'
SELECT REPLACE(mycolumn,'mytext','mysecondtext') FROM mytable	replace every 'mytext' with 'mysecondtext', ignore case of 'mytext'
SELECT REPLACE(myendcolumn, 'mytext','mysecondtext') FROM mytable	if 'mytext' is at the end on the line , replace with 'mysecondtext'
SELECT mycolumn, CASE WHEN 0= CHARINDEX('mytext', mycolumn,1) THEN mycolumn ELSE LEFT(mycolumn ,CHARINDEX('mytext' ,mycolumn,1)-1) +'mysecondtext' END AS mynewcol FROM mytable	replace everything after 'mytext' with 'mysecondtext'. replacing mytext and everything after mytext
SELECT CASE(LTRIM(ISNULL(mycolumn,''))) WHEN '' THEN 'mytext' ELSE mycolumn END	replace blanklines with 'mytext'. insert 'mytext' TIP:may need to ensure is truly blankline
SELECT IIF( mycolumn LIKE 'mytext%' ,'mysecondtext' + SUBSTRING( mycolumn ,LEN( 'mytext') +1 ,999), mycolumn) AS mynewcol,* FROM mytable	if begin column is 'mytext', replace with 'mysecondtext'
UPDATE mytable SET mysecondcolumn = 'mysecondtext' WHERE (mysecondcolumn ='mytext')	if second column is 'mytext', replace with 'mysecondtext'
SELECT *,CASE mysecondcolumn WHEN 'mytext' THEN 'myfourthtext' WHEN 'mysecondtext' THEN 'myfourthtext' ELSE '' END AS mynewcol FROM mytable	if second column is 'mytext' or 'mysecondtext' or 'mythirdtext' , replace with 'myfourthtext'
UPDATE mytable SET mycolumn = t2.mycolumn, mysecondcolumn = t2.mysecondcolumn FROM mytable t1 INNER JOIN mysecondtable t2 ON t2.mycolumn = t1.mycolumn WHERE t2.mycolumn= 'mytext'	replace mycolunm mysecondcolumn from fields in mysecondtable if mycolumn is 'mytext'
SELECT SUM(mythirdcolumn) ,mynewcol ,mycolumn FROM( SELECT mycolumn ,mythirdcolumn , CASE mycolumn WHEN 'mytext' THEN '1' WHEN 'mysecondtext' THEN '2' ELSE '3' END AS mynewcol FROM mytable) X GROUP BY mycolumn ,mynewcol	make new column and then sum on the new colunm
UPDATE mytable SET mysecondcolumn = 'mysecondtext', mythirdcolumn = 'mythirdtext' WHERE (mysecondcolumn ='mytext')	if second column is 'mytext', replace second column with 'mysecondtext', third column with 'mythirdtext'
SELECT IIF(mycolumn LIKE '%mytext',LEFT(mycolumn,LEN(mycolumn) -LEN( 'mytext'))+ 'mysecondtext', mycolumn) AS mynewcol,* FROM mytable	if end column is 'mytext', replace with 'mysecondtext'
SELECT REPLACE(mysecondcolumn,'mytext','mysecondtext') FROM mytable	if 'mytext' is anywhere in second column, replace with 'mysecondtext' ($NF if mytext is in end column)
SELECT *,CASE WHEN mycolumn ='mytext' THEN 'mythirdtext' WHEN mycolumn ='mysecondtext THEN 'myfourthtext' ELSE 'myothertext' END AS mynewcol FROM mytable	replace or delete or insert mytext or mysecondtext (many texts) using a list of multiple/duplicate texts in a file 'myreplacelist.txt'. ie: convert/normalise/clean text
SELECT UCASE(mycolumn) FROM mytable	replace/convert lines to uppercase
SELECT LCASE(mycolumn) FROM mytable	replace/convert lines to lowercase
SELECT mycolumn,CASE WHEN mycolumn LIKE '%mytext%' THEN mycolumn ELSE CASE WHEN mycolumn LIKE '%mysecondtext%' THEN 'mythirdtext' ELSE mycolumn END END AS mynewcol FROM mytable	replace 'mysecondtext' with 'mythirdtext', but not for lines with 'mytext'