Söka i början och slutet på SQL-strängar

Konstigt att jag under 10 år med SQL inte har behövt göra detta sökmönster. Hittade svaret på bytes.com:

startswith: WHERE x LIKE @Value + '%'

endswith: WHERE x LIKE '%' + @Value

contains: WHERE x LIKE '%' + @Value + '%'

Bakgrunden var att jag migrerade en sajt till nytt webbhotell. På detta fungerade det inte att ha filnamn på bilder som börjar på en punkt.

I mitt fall började alla mina bilder med punkt följd av ett underscore så jag kunde få fram alla med shjälp av följande sökning.

Har filnamnen i en db-tabell och fick fram dem med följande sökning:

SELECT [FileName] FROM [MediaItem] WHERE [FileName] LIKE  '._%'

Och sen bytte ut namnet på alla dessa så de startar med img istället för punkten.

UPDATE [MediaItem] SET [FileName]=REPLACE([FileName],'._','img_') WHERE [FileName] LIKE  '._%';

Sen för att byta ut namnen på själva filerna så hittade jag ett program som funkade, Bulk Rename Utility. Det verkar vara ett rätt avancerat verktyg och finns säkerligen mer lättanvända. Men funkade för mig.


By Jesper Lind
SQL Server

Ta bort mellanrum och tab-tecken från databas-värde

Jobbar med en databas där jag för länge sen hade importerat emailadresser från Excel eller liknande, kommer intee ihåg nu.

Märkte dock att det var massa tomma spaces efter själva emailadressern och letade efter ett sätt att ta bort dem. Detta svar kändes ju eom en bra lösning.

UPDATE [User] SET Email= LTRIM(RTRIM(Email))

Mellanrum kvarstod dock och då funderade jag istället på om de kanske var tab-tecken. Mycket riktigt efter ett tips så kunde jag köra det som behövdes.

UPDATE [User] SET Email=REPLACE(Email,char(9),'')

By Jesper Lind
SQL Server

SQL Replace för att skapa Slugs

Har på många tidigare projekt använt Id-nummer i url:erna för att IIS och .NET haft så dåligt stöd för att skapa användarvänliga url:er. Gör nu om en del gamla sidor till ASP.NET MVC och lägger över dem till IIS 7-servrar som har bättre stöd för url-omskrivning.

Brukar lägga till ett speciellt databas-fält för att spara den sista delen av url:en som ska vara till för att identifiera blogginlägget, produkten, kategorien eller vad det nu handlar om och basera denna på den äldre titeln. Använder det uttryck som även Wordpress använder för att beskriva ett sånt fält, "Slug".

Här är ett skript som jag precis körde på en sådan databas. Detta byter ut de tecken jag inte vill ha i url:erna mot ett vanligt bindestreck, gör om allt till gemener och byter ut å,ä,ö mot a,a,o.

UPDATE BlogPost SET Slug=Lower(Title);
UPDATE BlogPost SET Slug=REPLACE(Slug,'å','a');
UPDATE BlogPost SET Slug=REPLACE(Slug,'ä','a');
UPDATE BlogPost SET Slug=REPLACE(Slug,'ö','o');
UPDATE BlogPost SET Slug=REPLACE(Slug,' ','-');
UPDATE BlogPost SET Slug=REPLACE(Slug,'&','-');
UPDATE BlogPost SET Slug=REPLACE(Slug,'/','-');
UPDATE BlogPost SET Slug=REPLACE(Slug,'-','-');
UPDATE BlogPost SET Slug=REPLACE(Slug,'.','-');
UPDATE BlogPost SET Slug=REPLACE(Slug,',','-');
UPDATE BlogPost SET Slug=REPLACE(Slug,'_','-');
By Jesper Lind
IIS Server, SQL Server, URL-omskrivning

Filtrera DataSet

Ja även fast det är spännande att jobba med nya databas-tekniker som LinqToSQL och Subsonic, så kan det vara bra att kunna lite hederliga DataSet-operationer.

Just nu så hade jag ett problem med Subsonics Paging-funktioner, där en Where-sats inte kom med. Jag fick dubbla poster hur jag än gjorde. Då kom jag på att jag kunde filtrera DataSet:et i efterhand och skrev följande lilla funktion.

private static DataSet FilterDataSet(DataSet ds,string filter)
{
    DataSet clone = ds.Clone();

    DataRow[] foundRows = ds.Tables[0].Select(filter);

    for (int i = 0; i < foundRows.Length; i++)
    {
        DataRow row = foundRows[i];
        clone.Tables[0].ImportRow(row);
    }

    return clone;
}

Anropar den sedan på följande vis. I detta fallet ville jag ha ut texter på en visst språk, och inte alla språk i databas-tabellen som Subsonic envisade sig med att returnera.

return FilterDataSet(q.ExecuteDataSet(),"Culture='sv-SE'");

Är du sugen på liknade exempel, så kan jag rekommendera tidigare inlägg där vi skrivit om hur man sorterar ett DataTable eller hur man skapar nya kolumner i DataSet och sparar det i Cacheminne.

By Jesper Lind
ASP.NET, SQL Server, SubSonic

Reseed identity på SQL Server

Lärde mig just hur man gör så att nyckleln på en tabell i en SQL Server databas börjar på ett visst nummer. Bra om man gjort massa tester och vill återställa dem innan man börjar lägga in riktiga data.

-- Reseed identity on [dbo].[Products]
DBCC CHECKIDENT('[dbo].[Products]', RESEED, 0)
GO

Ännu bättre är ju ifall man inte lägger in data alls, utan istället använder transaktioner för att rulla tillbaks testdata. Läs mer hos Johan Lindfors, teknisk chef på Microsoft, om detta.

By Jesper Lind
SQL Server

Uppdatera databasfält till inledande versal följt av gemener

Vi hade en databas med massa postorter i versaler (ALINGSÅS, GÖTEBORG, STOCKHOLM) osv men ville ha dem så här istället (Alingsås, Göteborg, Stockholm).

Hittade tips i ett forum hur man gör.

Först för en SELECT för att testa:

SELECT UPPER(left(CityName, 1)) + substring(LOWER(CityName), 2, LEN(CityName))  FROM [Cities]

Och sen en UPDATE som ändrar fältet:

UPDATE Cities SET CityName = UPPER(left(CityName, 1)) + SUBSTRING(LOWER(CityName), 2, len(CityName))

By Jesper Lind
SQL Server

Ändra Collation på databas i SINGLE_USER-läge

Om man vill ändra Collation på en databas kan det vara svårt ifall man har andra användare uppkopplade. Detta blockerar kommandot och man får "The database could not be exclusively locked to perform the operation".

För att komma runt problemet kan man gå in i "SINGLE_USER"-läge (Glöm inte att gå tillbaks till "MULTI_USER" efter åt.

ALTER DATABASE <DBNAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE <DBNAME>
COLLATE SQL_SwedishStd_Pref_CP1_CI_AS;
GO

ALTER DATABASE <DBNAME> SET MULTI_USER
GO
By Jesper Lind
SQL Server

Jämnföra databasscheman och hålla ordning på versioner

När man utvecklar en webbapplikation (eller andra typer av program för den delen också) så är det ofta en stor utmaning att hålla strukturen på databaserna likadan. Man gör ändringar i sin orginalmodell och försöker ändra alla de databaser som är i drift enligt bästa förmåga. Hittills har jag inte haft något speciellt bra sätt att göra detta på utan det slutar ofta med felsökning steg för steg och ändra databasen manuellt. Tänkte här skriva om några sätt som kan förenkla detta jobb.

Lägga in databasskripten i källkodsprojektet

Ett sätt är att skripta ut hela databasen och sedan inkludera skripten i källkodsprojektet som Coding Horror förklarar. Inte helt på det klara om detta kan hjälpa en för att uppdatera befintliga databaser, men att ha strukturen i kod är ju ett bra första steg.

Använda sig av databas-migrering

Detta är ett koncept som funnits länge i Ruby On Rails-världen och innebär att varje förändring i databasen sparar i uppdateringsskript som man kan köra på sina databaser. 

Subsonic-teamet har nyligen inspirerats av Rails och lagt in liknande Migrations-funktionalitet i Subsonic. Har provat detta lite under sommaren och även fast det är väldigt ny teknik så verkar det fungera mycket bra.

Program för att Jämnföra databasscheman

Detta sätt tycker jag är det mest bekväma och innebär minst jobb. Man utgår helt enkels från sin orginalstruktur för databasen och jämför denna med de databaser som ska uppgraderas.

Denna funktionallitet finns i Visual Studio men bara i team-edition så den har jag inte testat själv. Verkar fungera fint och jag kan rekommendera läsning hos Emad Ibrahim som har provat på det.

Det smidigaste programmet som jag har provat är dock utan tvekan Redgate SQL Compare. Att jämföra två databaser går på nån minut och man får sedan SQL Skript redo att köra på den databas som ska uppgraderas. Har bara provat testversionen men funderar skarpt på att göra en investering i en licens.

Om du har några erfarenheter om hur man kan göra version-hantering av databaser lättare, så uppskattas kommentarer.

By Jesper Lind
SQL Server, Visual Studio, SubSonic