SQL Server Journey with SQL Authority

Posted in Pinal Dave, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 72 Comments

72 Responses

1. on November 14, 2007 at 5:37 pm | Reply Navab

   Really useful

   
   
2. on November 14, 2007 at 7:29 pm | Reply pergh

   Just what I wondered about.

   
   
3. on November 14, 2007 at 8:58 pm | Reply Poov

   Hi, It is really useful.

   I have the database – in which i have to generate script for 10 tables only – as i dont have Enterprise Manager, i want to generate the script through Query Analyser onl.

   ie the script should like the result of –Enterprise Manager—> Select the Table(s)–Rt Click–All Tasks — Generate SQL Script.

   How can i get this?

   Thanks in Advance.

   Regards — Poov

   
   
4. on January 18, 2008 at 5:18 pm | Reply Heidi

   Thnx!!
   This was just what I needed
   xx

   
   
5. on February 7, 2008 at 3:17 am | Reply Dave

   Thank you! Before this I found a bunch of other SQL’s _languages_ that accept just LENGTH, but THIS is what I really needed.

   Thank you!

   
   
6. on February 17, 2008 at 11:55 pm | Reply BADRI

   This is what I was looking for.
   Thanks

   
   
7. on March 28, 2008 at 8:07 pm | Reply Andrew Cullis

   Nice …

   but now how would i do that in Hibernate …

   : )

   
   
8. on June 22, 2008 at 3:12 am | Reply Ben Kimball

   Thanks for the info! Just what I was looking for…

   
   
9. on July 4, 2008 at 7:40 am | Reply Alain

   Thanks :) You saved me lots of trouble…

   
   
10. on July 16, 2008 at 9:16 pm | Reply Antonio

    I need some statement to check database field size in the code before inserting any value

    
    
11. on July 17, 2008 at 7:46 am | Reply Imran Mohammed

    @Antonio,

    You can use this stord procedure

    SP_HELP table_name

    it will list out all the fields, their sizes, constraints, indexes….

    It gives very good information.

    Hope this helps.

    Thanks,
    Imran.

    
    
    • on June 28, 2011 at 1:23 am | Reply Cortiney

      This is the most helpful thing EVER. Thank you!

      
      
12. on August 11, 2008 at 11:57 am | Reply anshul jindal

    thanks , very useful

    
    
13. on November 26, 2008 at 11:32 pm | Reply Hugo Estrada

    Thanks! You are the SQL man!

    
    
14. on January 14, 2009 at 11:40 pm | Reply Nisarg

    Vey helpful dude!! Keep up the good work!
    Jai Hind!

    
    
15. on January 22, 2009 at 8:56 pm | Reply Mark

    Thank you sir! Just what I was looking for!

    
    
16. on February 19, 2009 at 6:11 pm | Reply Andrey

    Thanks. Short and usefull info.

    
    
17. on March 17, 2009 at 1:02 pm | Reply Dickson

    Really useful…

    
    
18. on April 16, 2009 at 8:59 pm | Reply Ramani Sandeep

    i want to store more than 8000 chars in table field..
    i hv used varchar(max) , text(max) ,but i m not able to get success in it..

    everytime data gets truncated whn i insert it..

    so can u tell me how to overcome from this problem..?

    
    
    • on February 16, 2010 at 7:02 pm | Reply Madhivanan

      Note that there is no such datatype as text(max)

      How did you insert data?

      Can you post the code?

      
      
      • on July 17, 2011 at 4:39 pm | Reply Dilip Singh

        Are you use access database?
        if yes then take data type memo.
        if you use sqlserver 2005/08 there are no text data type
        there you can use nvarchar(max)

        
        
19. on July 6, 2009 at 4:12 am | Reply Chaminda

    I am having the same issue as Ramani. Any solution for this ?
    I have a text field and what ever number of characters that I save on that field, it returns only 1001 characters. But when I checked with DATALENGTH() function it shows that it is having the same number of characters that I entered.

    
    
20. on July 6, 2009 at 11:51 am | Reply Imran Mohammed

    @Chaminda,

    Open SQL Server Management Studio
    Go to Tools – Options (Tools exists at the top left of SSMS)
    Expand Query Results
    Expand SQL Server
    Click on Results to Grid

    On right side, Set Value for :
    Maximum Characters retrieved : Set a higher value.

    Because of this, SQL Server is returning fewer characters. Change this value to a higher value and see the difference

    ~ IM.

    
    
21. on July 20, 2009 at 9:43 pm | Reply Hozher

    Hello!

    But, when the field is null ?
    how can i know the size of a field, have or not have data in the table ??

    Thanks!

    
    
    • on February 16, 2010 at 7:03 pm | Reply Madhivanan

      When the value is NULL, the size is also NULL

      
      
22. on August 12, 2009 at 10:16 pm | Reply Kunal Shah

    Good work !!

    
    
23. on October 2, 2009 at 3:37 pm | Reply Rajesh

    Good tips!!!
    Thanks bro

    
    
24. on October 10, 2009 at 4:50 pm | Reply ran

    can anybody help me in using length function
    how i can make any field have 4 digits (e.g 7456)
    to 6 digits(i.e 007456) in my data base using sql query so that every field with length 4 is changed to length 6
    but zero should come in front

    
    
    • on February 16, 2010 at 7:05 pm | Reply Madhivanan

      It is formation issue. If you use front end application do the formation there

      
      
25. on October 12, 2009 at 10:44 am | Reply ran

    any one there
    plz reply

    
    
26. on October 13, 2009 at 2:29 am | Reply Brian Tkatch

    @ran

    Is this a VARCHAR field?

    UPDATE tab SET col = ’00′ + col WHERE LEN(col) = 4;

    
    
27. on November 11, 2009 at 4:12 pm | Reply UCMueller

    @ran

    UPDATE tab SET col = RIGHT(’000000′ + col, 6)

    
    
28. on November 17, 2009 at 8:37 am | Reply Bhargav

    Really great tip.
    Thanks mate.

    
    
29. on January 8, 2010 at 8:43 pm | Reply Lana

    Hi! This is not the first time your posts help me. Thank you so much!

    
    
30. on January 14, 2010 at 6:16 pm | Reply Devesh

    Thanks a lot for such a useful tip.

    
    
31. on January 15, 2010 at 3:51 pm | Reply Andazi

    Please help.
    Need to add/combine 3 columns

    Column1 column column3
    eddie lewis 125.87456

    query is select :

    column1+column2+cast(column3/100 as varchar(20))

    returns – eddielewis1.25874 i.e missing last 2 digits of column3 …… please help

    Thanks

    
    
    • on January 25, 2010 at 10:50 pm | Reply Gilad

      Hey Andazi,
      Change the query to

      column1+column2+cast(column3/100 as varchar(50))

      As the cast is only getting the first 20 characters

      
      
32. on January 20, 2010 at 4:01 am | Reply Carrie

    THIS HELPED ME SO MUCH – THANK YOU!!!

    
    
33. on January 23, 2010 at 8:33 pm | Reply Tom

    You guys explained how to change the data amount being returned directly in SQL server, but what about the amount being returned in an ASP page?

    
    
34. on February 11, 2010 at 7:31 pm | Reply pm

    This is great!

    But how can I find out the MAXSIZE of the field in question?

    We have recently updated our DB to increase the size of certain fields. I need to check if the user updated their DB before performing any work on those fields.

    
    
35. on February 16, 2010 at 8:58 pm | Reply Mike

    Hello,

    I have a table which has field1, field2 and field3 columns of type int, the combination of those columns represent 10 digit phone number. I know field3 always have 4 digits, but MS SQL trims the right most 0s. all i am tring to acheive is to provide the user with one field 10 digit number.

    here is my query:

    INSERT INTO [Info Direct Res new]
    SELECT ‘Info Direct’, /* Datbase Name */
    DECLARE @MAX_FIELD_LEN int;
    DECLARE @fieldLength int;
    SET @MAX_FIELD_LEN=4;
    SET @fieldLength = len(Cast(field3 as varchar));
    cast(field1 as varchar)+cast(field2 as varchar)+RIGHT(’0′,@MAX_FIELD_LEN-@fieldLength)+Cast(field3 as varchar), /* Phone Number */
    field5, /*First name*/
    field6, /*Last Name*/
    ISNULL(field8,”) + ‘ ‘ + ISNULL(field9,”)+ ‘ ‘ + ISNULL(field10,”)+ ‘ ‘ + ISNULL(field11,”)+ ‘ ‘ + ISNULL(field12,”), /* Full Address */
    field13, /* city */
    field14, /* province */
    field15, /* postal code */
    ‘ ‘
    from stcir1001

    I get syntax error where i say declare

    thanks,

    
    
36. on March 16, 2010 at 8:24 pm | Reply David

    Just what I needed, thank you

    
    
37. on April 7, 2010 at 8:04 pm | Reply Surender

    I have a tabe in oracle which has a varchar field with length of 48. I need to select records that are not in the length of either 8, 15 & 16 size.
    The second criteria is to identify records which are not numeric i.e. any character value should be displayed.

    
    
38. on July 21, 2010 at 12:42 pm | Reply Pralhad

    Thanks Alot.
    I got what i wanted since many days.
    I was trying LENGTH bat it wasnt working.
    Thanks alot once again.

    
    
39. on October 15, 2010 at 3:41 am | Reply aaronls

    My SQL related googling always leads me to your blog, and your articles are usually pretty helpful. Thanks!

    
    
40. on October 28, 2010 at 12:33 am | Reply abc

    The msdn website shows that len and datalength functions are different.

    LEN- Returns the number of characters of the specified string expression, excluding trailing blanks.

    DATALENGTH – Returns the number of bytes used to represent any expression.

    
    
41. on October 28, 2010 at 12:53 am | Reply Miriam

    Thanks a lot!

    I want to increase the text size in one specific DB. I see that the maximum text field size I already have is around 32,000 characters but text with 40,000 characters was truncated.

    How can i increase text size in specific DB or table?

    
    
    • on October 28, 2010 at 1:36 pm | Reply madhivanan

      Why do you need this? If you want to view full content, export to text file and view from there

      
      
      • on October 31, 2010 at 6:09 pm | Reply Miriam

        The problem is not to VIEW the content BUT to insert the whole text. the text is not inserted completely and is truncated…

        
        
42. on November 23, 2010 at 6:15 pm | Reply Vinod

    Sir can please you let me know that what is difference between LEN and DATALENGTH

    
    
43. on November 23, 2010 at 11:53 pm | Reply ogechi

    Thank you!

    
    
44. on January 26, 2011 at 3:29 pm | Reply Ranjeet kumar

    Hello All

    I want to fetch only 100 character from spacific column, plz tell me what is query for that.

    
    
    • on January 28, 2011 at 3:55 pm | Reply madhivanan

      select left(col,100) from table

      
      
45. on April 21, 2011 at 3:44 pm | Reply jaheer

    I am sending the output of a query to a file and unfortunately every row is containing only a certain number of characters. My query has rows having length more than 10000 characters for each row. When I get the query and send it to a file I am seeing only the first 250 characters for each row instead of 10000 characters.

    I will be grateful if anyone can help me suggesting a solution for this problem.

    I appreciate your help in advance.

    i tried this option which is not helping in my case as the max it support is 8192
    Go to Tools –> Options –> Results tab. Change the “Maximum characters per column” The default is 256

    
    
46. on June 7, 2011 at 7:24 pm | Reply pking

    jaheer,
    Not sure this helps but in SQL Server 2008 the maximum number of bytes per row is 8,060. This restriction is relaxed for tables with varchar, nvarchar, varbinary, or sql_variant columns that cause the total defined table width to exceed 8,060 bytes. The lengths of each one of these columns must still fall within the limit of 8,000 bytes, but their combined widths may exceed the 8,060 byte limit in a table. For more information, see Row-Overflow Data Exceeding 8 KB in BOL

    
    
47. on June 17, 2011 at 4:36 pm | Reply Srikanth Nallamothu

    ANOTHER USE OF LEN() AND DATALENGTH() ARE :-

    SELECT LEN(‘SRI ‘) – 3
    SELECT DATALENGTH(‘SRI ‘) – 9

    LEN() IGNORES THE SPACES BUT NOT DATALENGTH()

    
    
48. on August 3, 2011 at 12:17 pm | Reply VICKI

    Wow! Sooo simple. Used it. Loves it.

    
    
49. on August 4, 2011 at 12:51 pm | Reply jaheer

    pking,

    Thanks for the reply, i have found the solution for the issue.it simple we need to use the below command from sqlcmd to fetch a row having column with more than 10000 characters.

    sqlcmd -S 10.11.111.121 -U sa -P abc@123 -d DBNAMe -i ./conf/SQL.Archive_query.conf.sql -o ./tmp/sqlserver/SDS_Archive_Update.ddl -y 0

    -y 0 –> Using this option will increase the length of a row

    
    
50. on August 25, 2011 at 10:27 pm | Reply Amit

    How can i find the length of the memo field (ntext). through sql query.
    The len() function is not working for memo datatypes.

    kindly suggest

    Thanks
    Amit

    
    
    • on September 26, 2011 at 3:21 pm | Reply Ajay

      Amit, try using the command DATALENGTH().

      
      
51. on November 1, 2011 at 12:18 pm | Reply sathi

    Really nice

    
    
52. on February 2, 2012 at 8:21 pm | Reply Genxcat

    Thank you for having the perfect answer. I always come here first, as I have found most of what I have ever questioned answered here.

    
    
53. on March 2, 2012 at 8:48 pm | Reply steeler1

    i confirm, write to target…thanks

    
    
54. on May 23, 2012 at 7:06 am | Reply BrianB

    What is the best way to set up the table structure in SQL if I do not know the maximum length of all the fields?

    
    
    • on May 23, 2012 at 3:16 pm | Reply madhivanan

      for varcha use max, for numbers use BIGINT, etc

      
      
      • on May 24, 2012 at 12:42 am | Reply BrianB

        thanks!

        
        
55. on June 27, 2012 at 9:01 am | Reply kevinYen

    I was just wondering how to test if a column of text type is null
    This helps me a lot !!
    thanks

    
    
56. on July 9, 2012 at 8:22 pm | Reply San

    how do I get the len of a field content where fieldname is in a variable
    Follwing SQL does not return the max length of the content.

    ex: Declare @f1 as char(50);
    set @f1=”City”
    select max(len(@f1)) from

    
    
57. on August 2, 2012 at 9:28 am | Reply wisnurdi

    Thank you. Simple but this is the answer…

    
    
58. on August 29, 2012 at 5:04 am | Reply aditya

    thanks you..

    
    
59. on December 20, 2012 at 1:12 pm | Reply Erfan

    Hello

    How can we split a column which has arabic and english text.

    
    
60. on February 5, 2013 at 8:32 am | Reply Eric

    Thanks! Works perfectly

    
    

Leave a Reply Cancel reply

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (required) (Address never made public)

Name (required)

Website

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Twitter account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s

Notify me of follow-up comments via email.

Notify me of new posts via email.