CHARINDEX SQL function

1. Usage of CHARINDEX to find specific text.

SQL Server Query 1

            
SELECT CustomerID, CompanyName, CHARINDEX('er', CompanyName) AS PositionOf_er   

FROM Customers   

WHERE CHARINDEX('er', CompanyName) > 0;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select().Columns("CustomerID", "CompanyName")  

    .Column(new CHARINDEX("er", new Column("CompanyName")), "PositionOf_er")  

    .From("Customers")  

    .Where(new Where(new CHARINDEX("er", new Column("CompanyName")), SQLComparisonOperators.GREATER_THAN, 0))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT CustomerID,
       CompanyName,
       CHARINDEX(@pMAIN_2512060922574026790, CompanyName, 1) AS PositionOf_er
FROM Customers
WHERE CHARINDEX(@pMAIN_2512060922574026791, CompanyName, 1) > @pMAIN_2512060922574026792;


            
        

Parameters (If used)

Query Results 1:

2. Usage of CHARINDEX to find space position.

SQL Server Query 2

            
SELECT ProductName, CHARINDEX(' ', ProductName) AS FirstSpacePosition, CHARINDEX(' ',   

	ProductName, CHARINDEX(' ', ProductName) + 1) AS SecondSpacePosition   

FROM Products   

WHERE ProductName LIKE '% % %';  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select().Column("ProductName", "ProductName")  

    .Column(new CHARINDEX(" ", new Column("ProductName")), "FirstSpacePosition")  

    .Column(new CHARINDEX(" ", new Column("ProductName"), new CHARINDEX(" ", new Column("ProductName"), 1).AddArithmatic(new Arithmatic().Add(1))), "SecondSpacePosition")  

    .From("Products")  

    .Where(new Where(new LIKE(new Column("ProductName"), "% % %")))  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT ProductName AS ProductName,
       CHARINDEX(@pMAIN_2512060922574082450, ProductName, 1) AS FirstSpacePosition,
       CHARINDEX(@pMAIN_2512060922574082451, ProductName, CHARINDEX(@pMAIN_2512060922574082452, ProductName, 1) + @pMAIN_2512060922574082453) AS SecondSpacePosition
FROM Products
WHERE ProductName LIKE @pMAIN_2512060922574082454;


            
        

Parameters (If used)

Query Results 2:

3. Usage of CHARINDEX to products which contains 'sauce' keyword.

SQL Server Query 3

            
SELECT ProductID, ProductName   

FROM Products   

WHERE CHARINDEX('Sauce', ProductName) > 0;  

            
        

Create SQL query with SqlQueryBuilder 3

            
var (sql3, parameters3) = new SqlQueryBuilder()  

    .Select().Columns("ProductID", "ProductName")  

    .From("Products")  

    .Where(new Where(new CHARINDEX("Sauce", new Column("ProductName")), SQLComparisonOperators.GREATER_THAN, 0))  

    .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT ProductID,
       ProductName
FROM Products
WHERE CHARINDEX(@pMAIN_2512060922574154650, ProductName, 1) > @pMAIN_2512060922574154651;


            
        

Parameters (If used)

Query Results 3:

4. Usage of CHARINDEX to find company name which contains space.

SQL Server Query 4

            
SELECT CustomerID, CompanyName,   

	SUBSTRING(CompanyName, 1, CHARINDEX(' ', CompanyName) - 1) AS FirstWord   

FROM Customers   

WHERE CHARINDEX(' ', CompanyName) > 0; -- Only show companies with at least one space  

            
        

Create SQL query with SqlQueryBuilder 4

            
var (sql4, parameters4) = new SqlQueryBuilder()  

    .Select().Columns("CustomerID", "CompanyName")  

    .Column(new SUBSTRING(new Column("CompanyName"), 1, new CHARINDEX(" ", new Column("CompanyName"), 1).AddArithmatic(new Arithmatic().Subtract(-1))), "FirstWord")  

    .From("Customers")  

    .Where(new Where(new CHARINDEX(" ", new Column("CompanyName")), SQLComparisonOperators.GREATER_THAN, 0))  

    .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT CustomerID,
       CompanyName,
       SUBSTRING(CompanyName, @pMAIN_2512060922574170770, CHARINDEX(@pMAIN_2512060922574170771, CompanyName, 1) - @pMAIN_2512060922574170772) AS FirstWord
FROM Customers
WHERE CHARINDEX(@pMAIN_2512060922574170773, CompanyName, 1) > @pMAIN_2512060922574170774;


            
        

Parameters (If used)

Query Results 4:

5. Usage of CHARINDEX to find last name of employees having 'e' character.

SQL Server Query 5

            
SELECT EmployeeID, FirstName, LastName   

FROM Employees   

WHERE CHARINDEX('e', LastName COLLATE Latin1_General_CI_AI) > 0;  

            
        

Create SQL query with SqlQueryBuilder 5

            
var (sql5, parameters5) = new SqlQueryBuilder()  

    .Select().Columns("EmployeeID", "FirstName", "LastName")  

    .From("Employees")  

    .Where(new Where(new CHARINDEX("e", new Column("LastName", Collate.Latin1_General_CI_AI)).GreaterThan(0)))  

    .Build();  

            
        

Query build by SqlQueryBuilder 5

            
SELECT EmployeeID,
       FirstName,
       LastName
FROM Employees
WHERE CHARINDEX(@pMAIN_2512060922574204780, LastName COLLATE Latin1_General_CI_AI, 1) > @pMAIN_2512060922574204781;


            
        

Parameters (If used)

Query Results 5:

6. Usage of CHARINDEX to find ampersand in category name.

SQL Server Query 6

            
SELECT CategoryName,   

	CASE WHEN CHARINDEX('&', CategoryName) > 0   

		 THEN 'Contains Ampersand'   

		 ELSE 'No Ampersand' END AS AmpersandCheck   

FROM Categories;  

            
        

Create SQL query with SqlQueryBuilder 6

            
var (sql6, parameters6) = new SqlQueryBuilder()  

    .Select().Columns("CategoryName")  

    .Column(new CASE()  

        .When(new CHARINDEX("&", new Column("CategoryName")).Equale(0))  

        .Then("Contains Ampersand")  

        .Else("No Ampersand"), "AmpersandCheck")  

    .From("Categories")  

    .Build();  

            
        

Query build by SqlQueryBuilder 6

            
SELECT CategoryName,
       CASE WHEN CHARINDEX(@pMAIN_2512060922575204440, CategoryName, 1) = @pMAIN_2512060922575204441 THEN @pMAIN_2512060922575204442 ELSE @pMAIN_2512060922575204443 END AS AmpersandCheck
FROM Categories;


            
        

Parameters (If used)

Query Results 6: