Extarcting string after and before a Character/Pattern

  • Comments posted to this topic are about the item Extarcting string after and before a Character/Pattern

  • More of an article than a script, but good basic knowledge. Thanks.

  • This was good the last time I looked at it. Still is.

  • ----select characters before / including /

    select SUBSTRING ('abcde/wxyz',0,CHARINDEX('/','abcde/wxyz')+1)

    I'm confused with the code above. Isn't the position of the first character of a string always = 1 ?

    I ran these queries:

    SELECT SUBSTRING ( 'abcde/wxyz', 0, 1 ) => returns nothing

    SELECT SUBSTRING ( 'abcde/wxyz', 1, 1 ) => returns 'a'

    Based on this, shouldn't the query be ?

    ----select characters before / including /

    select SUBSTRING ('abcde/wxyz', 1, CHARINDEX('/','abcde/wxyz'))

    Thank you

  • Instead of using a literal string, can you use a table name in its place to represent the values within it? I'm currently trying to do an exercise that requires me to show the characters after a "-". This is my exercise: Write a query that uses the SUBSTRING function and the CHARINDEX function to display the characters in the product number following the hyphen. I'm using the AdventureWorks2019 Database with values from the Production.Product table. I've provided a code sample example from a discussion post here at SQL Server Central, but I altered it to fit my exercise. Unfortunately, when I executed the code, the result table showed the characters after the "-", but they weren't the same as the original table. I used a SELECT * FROM [Production].[Product] to show that the characters differed.

    SELECT * FROM [Production].[Product]

    SELECT SUBSTRING(ProductNumber,
    CHARINDEX('-', ProductNumber) +1,
    LEN(ProductNumber) - CHARINDEX('-', ProductNumber))
    FROM [Production].[Product]
    Attachments:
    You must be logged in to view attached files.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply