SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Easy-Query if anyone can help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cqldba303
Starting Member

16 Posts

Posted - 09/08/2010 :  15:02:11  Show Profile  Reply with Quote
I have one column(serial) in table(db1)
the contents of this column are:
DE328202668
PT449263719
SE354001059
DE319501516

I want to write a query which will add string(_072010) to all the contents of this column.
so after I run the query the content of the column(serial) should look like this:
DE328202668_072010
PT449263719_072010
SE354001059_072010
DE319501516_072010

Please let me know easy way to do this. I have 2000 rows in this column so query is easy.
thanks

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 09/08/2010 :  15:07:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
UPDATE YourTable
SET YourColumn = YourColumn + '_072010'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

cqldba303
Starting Member

16 Posts

Posted - 09/08/2010 :  15:14:06  Show Profile  Reply with Quote
it gives me error:

Server: Msg 8152, Level 16, State 9, Line 1
The string or binary data would be warped.
Statement has been terminated.
Go to Top of Page

cqldba303
Starting Member

16 Posts

Posted - 09/08/2010 :  15:15:20  Show Profile  Reply with Quote
it sql 2000, something different should do ?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 09/08/2010 :  15:18:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
Your column isn't wide enough to fit the data. Increase its size.

What data type are you using and what is its size?

Also, what's the maximum length of the column? SELECT MAX(DATALENGTH(YourColumn)) FROM YourTable

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

cqldba303
Starting Member

16 Posts

Posted - 09/08/2010 :  15:27:06  Show Profile  Reply with Quote
You were write, it was size of the column.
But I got another issue:
Column Type is:varchar
By default size is 50
My query works with that but it gives this result
DE328202668 _072010
PT449263719 _072010
SE354001059 _072010
DE319501516 _072010
How can i remove that GAP ?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 09/08/2010 :  15:28:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
Use the RTRIM function as it appears you've got a trailing space.

UPDATE YourTable
SET YourColumn = RTRIM(YourColumn) + '_072010'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

cqldba303
Starting Member

16 Posts

Posted - 09/08/2010 :  15:30:23  Show Profile  Reply with Quote
Problem is all the content of my column(serial), the size is not the same.
Some content size is 10, somewhere 5 character and somewhere 20 character, it is not consistent size content of that column.
but the string i want to attach (_072010) should be exactly the end of each content.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 09/08/2010 :  15:41:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
The size doesn't matter for the queries I posted.

Your data has the gap in it, my queries didn't add that. Use RTRIM function to fix.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

cqldba303
Starting Member

16 Posts

Posted - 09/08/2010 :  16:03:05  Show Profile  Reply with Quote
COOL
Thanks for fixing. You seem like living by your name :)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 09/08/2010 :  16:03:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000