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 |