Other DATALENGTH SQL function


1. Usage of DATALENGTH with customers

SQL Server Query 1

            
 SELECT CustomerID, DATALENGTH(CustomerID) AS CustomerIDDataLength FROM Customers;   

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  
.Select().Column("CustomerID", "CustomerID")
.Column(new DATALENGTH(new Column("CustomerID")), "CustomerIDDataLength")
.From("Customers")
.Build();

Query build by SqlQueryBuilder 1

            
SELECT CustomerID AS CustomerID,
       DATALENGTH(CustomerID) AS CustomerIDDataLength
FROM Customers;


            
        

Parameters (If used)

Name Value

Query Results 1:

  CustomerID CustomerIDDataLength
1 ALFKI 10
2 ANATR 10
3 ANTON 10
4 AROUT 10
5 BERGS 10
6 BLAUS 10
7 BLONP 10
8 BOLID 10
9 BONAP 10
10 BSBEV 10
11 CACTU 10
12 CENTC 10
13 CHOPS 10
14 CONSH 10
15 DRACD 10
16 DUMON 10
17 EASTC 10
18 ERNSH 10
19 FISSA 10
20 FOLIG 10
21 FOLKO 10
22 FRANK 10
23 FRANR 10
24 FRANS 10
25 FURIB 10
26 GALED 10
27 GODOS 10
28 KOENE 10
29 LACOR 10
30 LAMAI 10
31 LEHMS 10
32 MAGAA 10
33 MAISD 10
34 MORGK 10
35 NORTS 10
36 OCEAN 10
37 OTTIK 10
38 PARIS 10
39 PERIC 10
40 PICCO 10
41 PRINI 10
42 QUICK 10
43 RANCH 10
44 REGGC 10
45 RICSU 10
46 ROMEY 10
47 SANTG 10
48 SEVES 10
49 SIMOB 10
50 SPECD 10
51 SUPRD 10
52 TOMSP 10
53 TORTU 10
54 VAFFE 10
55 VICTE 10
56 VINET 10
57 WANDK 10
58 WARTH 10
59 WILMK 10
60 WOLZA 10
61 OLDWO 10
62 BOTTM 10
63 LAUGB 10
64 LETSS 10
65 HUNGO 10
66 GROSR 10
67 SAVEA 10
68 ISLAT 10
69 LILAS 10
70 THECR 10
71 RATTC 10
72 LINOD 10
73 GREAL 10
74 HUNGC 10
75 LONEP 10
76 THEBI 10
77 MEREP 10
78 HANAR 10
79 QUEDE 10
80 RICAR 10
81 COMMI 10
82 FAMIA 10
83 GOURL 10
84 QUEEN 10
85 TRADH 10
86 WELLI 10
87 HILAA 10
88 LAZYK 10
89 TRAIH 10
90 WHITC 10
91 SPLIR 10


2. Usage of DATALENGTH in WHERE clause

SQL Server Query 2

            
 SELECT ProductName   
FROM Products
WHERE DATALENGTH(ProductName) - 5 > 20; -- Find products with a name longer than 10 Unicode characters (20 bytes)

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  
.Select().Column("ProductName", "ProductName")
.From("Products")
.Where(new Where(new DATALENGTH(new Column("ProductName"))
.AddArithmatic(new Arithmatic().Subtract(5))
.GreaterThan(20)))
.Build();

Query build by SqlQueryBuilder 2

            
SELECT ProductName AS ProductName
FROM Products
WHERE DATALENGTH(ProductName) - @pMAIN_2507200154055384210 > @pMAIN_2507200154055384211;


            
        

Parameters (If used)

Name Value
@pMAIN_2507200154055384210 5
@pMAIN_2507200154055384211 20

Query Results 2:

  ProductName
1 Aniseed Syrup
2 Boston Crab Meat
3 Camembert Pierrot
4 Carnarvon Tigers
5 Chartreuse verte
6 Chef Anton's Cajun Seasoning
7 Chef Anton's Gumbo Mix
8 Côte de Blaye
9 Escargots de Bourgogne
10 Gnocchi di nonna Alice
11 Gorgonzola Telino
12 Grandma's Boysenberry Spread
13 Guaraná Fantástica
14 Gudbrandsdalsost
15 Gumbär Gummibärchen
16 Gustaf's Knäckebröd
17 Jack's New England Clam Chowder
18 Laughing Lumberjack Lager
19 Longlife Tofu
20 Louisiana Fiery Hot Pepper Sauce
21 Louisiana Hot Spiced Okra
22 Manjimup Dried Apples
23 Mascarpone Fabioli
24 Mishi Kobe Niku
25 Mozzarella di Giovanni
26 Nord-Ost Matjeshering
27 Northwoods Cranberry Sauce
28 NuNuCa Nuß-Nougat-Creme
29 Original Frankfurter grüne Soße
30 Outback Lager
31 Perth Pasties
32 Queso Cabrales
33 Queso Manchego La Pastora
34 Raclette Courdavault
35 Ravioli Angelo
36 Rhönbräu Klosterbier
37 Rössle Sauerkraut
38 Sasquatch Ale
39 Schoggi Schokolade
40 Scottish Longbreads
41 Singaporean Hokkien Fried Mee
42 Sir Rodney's Marmalade
43 Sir Rodney's Scones
44 Sirop d'érable
45 Steeleye Stout
46 Tarte au sucre
47 Teatime Chocolate Biscuits
48 Thüringer Rostbratwurst
49 Uncle Bob's Organic Dried Pears
50 Valkoinen suklaa
51 Wimmers gute Semmelknödel
52 Zaanse koeken