相关文章推荐
豪情万千的围巾  ·  B2C建站价格分析-乾元坤和官网·  4 月前    · 
大方的人字拖  ·  法律非法学和法律法学的区别-掌上考研·  5 月前    · 
老实的稀饭  ·  君智战略咨询-7年助力7企破百亿·  5 月前    · 
机灵的西瓜  ·  MindMaster软件的个人空间-Mind ...·  5 月前    · 
小眼睛的大象  ·  it's my life ...·  1 年前    · 
小百科  ›  SQL CHARINDEX
position substring charindex
曾深爱过的电池
1 年前
  • expression_to_find : In this parameter, we specify a character or string that we want to search in another string
  • expression_to_search: We can specify a string or sentence in which we want to search expression_to_find
  • start_location: It is an optional parameter. We can specify an integer value in this parameter to specify start location. If we want to search expression_to_find in expression_to_search with a specified start location, we can specify it. By default, if we do not mention any value in this parameter, it starts a search from the index position 0

    Example 1: Search a character position in a string

    In this example, we want to find position of @ character in a specified email address [email protected] In the following screenshot, we can see position of each character in the email address string. The character ‘ @’ is in position 17. We get this position in output of SQL CHARINDEX. Suppose we want to get the position of the second dot (.) in this email address. We can specify a value for an optional parameter to start searching from a specific position. It starts from a specific character position and checks for the character position. We still get the actual position of the character that is 23rd in this example.

    Example 3: Search a substring position in a specified string in SQL CHARINDEX

    In previous examples, we searched a specific character in a specified string. We can also search a substring as well in a string. In the following query, we declared a variable @ExpressionToSearch for the string and set a value on it. We want to search for substring Rajendra in this string.

    Example 4: Search a substring position in a specified string with multiple matching in SQL CHARINDEX

    Suppose we want to search a substring in a specified string. In the string we have multiple matching substrings. For example, in the following query, we want to search for SQLShack and find its position.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    DECLARE @ Name AS VARCHAR ( 100 ) = 'Explore SQL Server with articles on SQLShack' ;
    SELECT CASE
    WHEN CHARINDEX ( 'SQLShack' , @ Name ) > 0
    THEN 'Exists'
    ELSE 'Not Exists'
    END AS FindSubString ;
    SELECT CASE
    WHEN CHARINDEX ( 'Rajendra' , @ Name ) > 0
    THEN 'Exists'
    ELSE 'Not Exists'
    END AS FindSubString ;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    DECLARE @ ExpressionToSearch varchar ( 100 )
    SET @ ExpressionToSearch = 'Explore SQL Server on SQLSHACK with Rajendra Gupta articles '
    SELECT CHARINDEX ( 'sqlshack' , @ ExpressionToSearch COLLATE Latin1_General_CS_AS ) AS 'CharacterPosition'
    SELECT CHARINDEX ( 'SQLShack' , @ ExpressionToSearch COLLATE Latin1_General_CS_AS ) AS 'CharacterPosition'
    SELECT CHARINDEX ( 'SQLSHACK' , @ ExpressionToSearch COLLATE Latin1_General_CS_AS ) AS 'CharacterPosition'
    If the target string varchar(max), nvarchar(max), it returns Big Int value else it returns Int data type By default, it performs a case insensitive search If there is no match found, it returns 0 in return

    Conclusion

    In this article, we explored SQL CHARINDEX function and its usage with various examples. Please feel free to provide feedback or ask questions in the comment section below.
    Rajendra Gupta
    Latest posts by Rajendra Gupta ( see all )

    Related posts:

    1. Substring function overview
    2. SQL STUFF function overview
    3. SQL Order by Clause overview and examples
    4. Overview of SQL LOWER and SQL UPPER functions
    5. T-SQL RegEx commands in SQL Server

      6. About Rajendra Gupta

      Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience. I am the author of the book " DP-300 Administering Relational Database on Microsoft Azure ". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines. I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups . Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020. Personal Blog: https://www.dbblogger.com I am always interested in new challenges so if you need consulting help, reach me at [email protected] View all posts by Rajendra Gupta

    6. SQL Convert Date functions and formats
    7. SQL PARTITION BY Clause overview
    8. SQL Variables: Basics and usage
    9. Different ways to SQL delete duplicate rows from a SQL Table
    10. How to UPDATE from a SELECT statement in SQL Server
    11. SELECT INTO TEMP TABLE statement in SQL Server
    12. Understanding the SQL MERGE statement
    13. SQL WHILE loop with simple examples
    14. How to backup and restore MySQL databases using the mysqldump command
    15. Overview of SQL RANK functions
    16. SQL Server functions for converting a String to a Date
    17. SQL Server table hints – WITH (NOLOCK) best practices
    18. The Table Variable in SQL Server
    19. SQL multiple joins for beginners with examples
    20. SQL CROSS JOIN with examples
    21. SQL Lag function overview and examples
    22. INSERT INTO SELECT statement overview and examples
    23. CASE statement in SQL
    24. Understanding the SQL Decimal data type
    25. SQL percentage calculation examples in SQL Server
    26. How to restore a SQL Server database backup to an older version of SQL Server
    •  
    推荐文章
    豪情万千的围巾  ·  B2C建站价格分析-乾元坤和官网
    4 月前
    大方的人字拖  ·  法律非法学和法律法学的区别-掌上考研
    5 月前
    老实的稀饭  ·  君智战略咨询-7年助力7企破百亿
    5 月前
    机灵的西瓜  ·  MindMaster软件的个人空间-MindMaster软件个人主页-哔哩哔哩视频
    5 月前
    小眼睛的大象  ·  it's my life 这是我的人生- 知乎
    1 年前
    Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
    小百科 - 百科知识指南