none
BI Developer RRS feed

  • Question


  • Hi All

    Please help to write sql query that can extract the name Carlo and Liyabona. Please note I dont know how any characters can be at the left or right of the name Carlo or Liyabona

    mnby/jfdkjds/Carlo -- >kjksjs/gsl

    sss/kvlkfklkvlfkl/Liyabona-- >gsssssskgjs/kfs

    Kind Regards

    Saturday, December 7, 2019 8:25 PM

All replies

  • SELECT iif(charindex(@val, col) > 0, 
               substring(col, charindex(@val, col), len(@val)),
               null)
    FROM   tbl


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, December 7, 2019 9:58 PM
  • ,('sss/kvlkfklkvlfkl/Liyabona-- >gsssssskgjs/kfs')
    --,('')
    --,(null)
    
    select   
    Rtrim(parsename(replace(Stuff(col,charindex('-- >', col),len(col),''),'/','.'),1)) val
    from test
    
    drop table test

    Saturday, December 7, 2019 11:52 PM
    Moderator

  • mnby/jfdkjds/Carlo -- >kjksjs/gsl

    sss/kvlkfklkvlfkl/Liyabona-- >gsssssskgjs/kfs

    Please try below query and correct me if I misunderstood the requirement.

    CREATE TABLE myTable (myValue VARCHAR(100))
    
    INSERT INTO myTable
    VALUES ('mnby/jfdkjds/Carlo'), ('sss/kvlkfklkvlfkl/Liyabona')
    
    SELECT *, replace(replace(myValue, '/Carlo', ''), '/Liyabona', '') AS NewValue
    FROM myTable


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Sunday, December 8, 2019 5:58 AM
  • Hi Elly-Joy, 

    Thank you for your issue . 

    Per your description , your data might be 'mnby/jfdkjds/Carlo' and is 'kjksjs/gsl' your expected result ? I am sorry that I might be unclear for your result . Could you please explain more ?

    Or your data  is 'mnby/jfdkjds/Carlo -- >kjksjs/gsl' and your expected result is 'mnby/jfdkjds/ -- >kjksjs/gsl'. Right ? If so , please try following script. 

    IF OBJECT_ID('test') IS NOT NULL drop table  test   
    go 
    CREATE TABLE test (myValue VARCHAR(50))
    
    INSERT INTO test
    VALUES ('mnby/jfdkjds/Carlo -- >kjksjs/gsl'), ('sss/kvlkfklkvlfkl/Liyabona-- >gsssssskgjs/kfs')
    
    SELECT case when charindex('Carlo', myValue) > 0 then stuff(myValue, charindex('Carlo', myValue), len('Carlo'),'')
               when charindex('Liyabona', myValue) > 0 then stuff(myValue, charindex('Liyabona', myValue), len('Liyabona'),'')
    		   else myValue end myValue
    FROM   test
    /*
    myValue
    ---------------------------------------------------------------
    mnby/jfdkjds/ -- >kjksjs/gsl
    sss/kvlkfklkvlfkl/-- >gsssssskgjs/kfs
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 9, 2019 7:22 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 17, 2019 8:47 AM