none
How to substring query result? RRS feed

  • Question

  • I need to make a query in which user will give input and query will find out different fields in database for the word using LIKE clause....and tht is what i am done with!
    But the return data of each columns is soooo big, I want that result to trim down or substring so that the few words will appear with searched keyword in middle of it somewhere (just like we use to see in google)
    plzz help me !!!
    Monday, April 27, 2009 2:33 PM

Answers

  • select substring(youfieldname,1,100) as shortfield ,... from youtable where ....

    family as water
    Monday, April 27, 2009 3:00 PM
  • You may use the soundex method

    http://msdn.microsoft.com/en-us/library/ms187384.aspx

    http://databases.about.com/od/development/l/aasoundex.htm


    this method will increase your performance to search as well

    The following example shows the SOUNDEX function and the related DIFFERENCE function. In the first example, the standard SOUNDEX values are returned for all consonants. Returning the SOUNDEX for Smith and Smythe returns the same SOUNDEX result because all vowels, the letter y, doubled letters, and the letter h, are not included.

    -- Using SOUNDEX
    SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe');

    Here is the result set.

    ----- ----- 
    S530  S530  
    
    (1 row(s) affected)

    The DIFFERENCE function compares the difference of the SOUNDEX pattern results. The following example shows two strings that differ only in vowels. The difference returned is 4, the lowest possible difference.

    -- Using DIFFERENCE
    SELECT DIFFERENCE('Smithers', 'Smythers');
    GO

    Here is the result set.

    ----------- 
    4           
    
    (1 row(s) affected)

    In the following example, the strings differ in consonants; therefore, the difference returned is 2, the greater difference.

    SELECT DIFFERENCE('Anothers', 'Brothers');
    GO

    Here is the result set.

    ----------- 
    2           
    
    (1 row(s) affected)


     
    Meng Chew
    Tuesday, April 28, 2009 1:11 AM
  • I see,
    First thing in my mind from your request will be the performance issues.
    Searching from a large text fields using like or wildcard will impact the performance, but you need to futher substring the result to show only the portion contain the string.

    Are you really need the substring potion and give up the performance?

    Perhap you may enhance on the program itself instead in the SQL statement
    Meng Chew
    Wednesday, April 29, 2009 5:16 AM

All replies

  • Have you tried SubString()?
    Monday, April 27, 2009 2:47 PM
    Moderator
  • select substring(youfieldname,1,100) as shortfield ,... from youtable where ....

    family as water
    Monday, April 27, 2009 3:00 PM
  • You may use the soundex method

    http://msdn.microsoft.com/en-us/library/ms187384.aspx

    http://databases.about.com/od/development/l/aasoundex.htm


    this method will increase your performance to search as well

    The following example shows the SOUNDEX function and the related DIFFERENCE function. In the first example, the standard SOUNDEX values are returned for all consonants. Returning the SOUNDEX for Smith and Smythe returns the same SOUNDEX result because all vowels, the letter y, doubled letters, and the letter h, are not included.

    -- Using SOUNDEX
    SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe');

    Here is the result set.

    ----- ----- 
    S530  S530  
    
    (1 row(s) affected)

    The DIFFERENCE function compares the difference of the SOUNDEX pattern results. The following example shows two strings that differ only in vowels. The difference returned is 4, the lowest possible difference.

    -- Using DIFFERENCE
    SELECT DIFFERENCE('Smithers', 'Smythers');
    GO

    Here is the result set.

    ----------- 
    4           
    
    (1 row(s) affected)

    In the following example, the strings differ in consonants; therefore, the difference returned is 2, the greater difference.

    SELECT DIFFERENCE('Anothers', 'Brothers');
    GO

    Here is the result set.

    ----------- 
    2           
    
    (1 row(s) affected)


     
    Meng Chew
    Tuesday, April 28, 2009 1:11 AM
  • Hi Meng Chew,
    I just need to know how to trim down the length of my returned column data?
    Thank you for your reply by the way! :)
    Wednesday, April 29, 2009 4:56 AM
  • I guess you need to enable Full Text Catalog and use the feature of Ranking,

    Refer Full Text Catalog
    Be the change you want.
    Wednesday, April 29, 2009 5:05 AM
  • I see,
    First thing in my mind from your request will be the performance issues.
    Searching from a large text fields using like or wildcard will impact the performance, but you need to futher substring the result to show only the portion contain the string.

    Are you really need the substring potion and give up the performance?

    Perhap you may enhance on the program itself instead in the SQL statement
    Meng Chew
    Wednesday, April 29, 2009 5:16 AM
  • SELECT

     

    dataCol1, case when len(dataCol1) - charindex(@urKeyWord , dataCol1 ) > 50 then substring(dataCol1,charindex(@urKeyWord , dataCol1 ),charindex(@urKeyWord , dataCol1 ) + 50 ) else substring(dataCol1,charindex(@urKeyWord , dataCol1 ),len(@urKeyWord) ) end

    FROM

     

    myTable

    WHERE

     

    dataCol2 NOT IN (SELECT col FROM anotherTable)

    Wednesday, April 29, 2009 9:48 AM