Substring() Function

substring() — Returns the first N characters of a string.

Syntax

The substring() function returns a string, and takes 3 parameters, a tree and two strings:

string substring(tree input, string input, string input)

Return value

The substring() function returns a string.

Description

The substring() function returns the first N characters of a string.

Examples

You can try the following examples against your own data in the GoToAssist search field, or against fictitious data in the Query Sandbox:

• Example 1: Find the first 11 characters
• Example 2: Diplay 11 characters
• Example 3: Diplay top mailbox distribution

Example 1: Find the first 11 characters

Query: Find and display the first 11 characters of each device name.

 SELECT substring(name, 0, 11)
 FROM /network/device/wmi/win32_product

Results: This query displays the first 11 characters of each device name:

row
   substring(name, 0, 11) WebFldrs XP
row
   substring(name, 0, 11) Jasc Paint
row
   substring(name, 0, 11) Symantec An 
   . . .

Example 2: Display the first 11 characters

Query: Display 11 characters of each device name, starting with the 11th character.

SELECT substring(name, 11, 11)
 FROM /network/device/wmi/win32_product

Results: This query displays each device name starting with the 11th character:

row
   substring(name, 11, 11) Shop Photo
row
   substring(name, 11, 11) tiVirus
row
   substring(name, 11, 11) P2 (KB93618  
   . . .

Example 3: Top mailbox distribution

Query: Find the distribution of mailbox size.

SELECT substring(mailboxdisplayname, 0, 5), 
   abbreviate(mailboxdisplayname, 30) as "Name", 
   totalitems as "# Items", 
   format(size * 1024, 'human_bytes') as Size 
FROM /network/device/wmi/exchange_mailbox 
order trees by size desc

Results: Depending on the database, this query returns results similar to this:

row
   substring(mailboxdisplayname, 0, 5)bill
   Name: sam jones
   # Items: 362
   Size: 7.83 MB
row
   substring(mailboxdisplayname, 0, 5)chris
   Name: delia auckford
   # Items: 265
   Size: 6.92 MB
row
   substring(mailboxdisplayname, 0, 5)Syste
   Name: SystemMailbox{679BAD05-A172...
   # Items: 401
   Size: 361.00 KB 
   . . .

Related topics

• Functions
• Operators
• Expressions
• Read about PQL

| Views: 1274 | Last Updated: Thu, May 09 2013 2:28 PM

Is this article helpful?