October 2, 2009 at 1:54 pm
I am doing testing for the substring to be applied in my query like this and
1. This works
use AdventureWorks
select FirstName from Person.Contact
2. but this gives error
declare @string1 varchar(100)
set @string1 ='FirstName MiddleNameLastName '
select substring(@string1,1,10) First from Person.Contact
Please comment.
October 2, 2009 at 2:21 pm
What's the error? Also, why are you selecting a variable from a table?
This works fine:
declare @string1 varchar(100)
set @string1 ='FirstName MiddleNameLastName '
select substring(@string1,1,10) FIRST
October 2, 2009 at 2:27 pm
Thanks for the response.
But I got all "FirstName" string for the result instead of getting the real content of "FirstName" column.
October 2, 2009 at 2:35 pm
It's giving you what you asked it to do. You've essentially asked it to return the constant value of "FirstName" for every row in person.contact (where person is the schema and Contact the table name).
In order to get the first name from the table, you need to replace @strng1 in your SELECT statement with the name of the column that has the full name in it.
Not having the specifics - I suspect you are looking for something like
select substring(contact,1,10) as FirstName from Person
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 2, 2009 at 2:39 pm
Of course you did, that's what you selected :hehe:.
Are you trying to build a string that is FirstName + MiddleName + LastName?
If so, you're using the wrong function.
SELECT FirstName + ' ' + MiddleName + ' ' + LastName
FROM Person.Contact
If this is not what you're attempting to do, please supply the output you are attempting to get.
Also note that this does not handle the NULLS you're going to get from this oversimplified concatenation.
October 2, 2009 at 2:41 pm
Thank you for response again and I do understand.
If you do not mind, could you please show me how to do it IF using my "string1" instead of getting from the real column? Because this is just a simple case out from a complex one.
Thanks.
October 2, 2009 at 3:21 pm
First thing I'll do is point you to BOL (Books Online, the SQL Server Help System that can be accessed by pressing the {f1} function key while in SSMS).
You want to look for Dynamic SQL. Once you have read up on that a bit, we can help you more.
October 2, 2009 at 3:31 pm
3 different people have now responded with 4 different guesses as to what you're trying to do here. 😛 Please try to explain better.
It is often a mistake to attempt to 'oversimplify' your question when you don't fully understand what's going on to begin with.
Can you post the actual code that you have, what that code produces, and what you'd like it to produce instead? That may be enough info for us to figure out what you're attempting. (We may still need more info, but that may be enough)
By all means change any sensitive information this would present, but make sure you change it consistently. (ie. Don't change the same number to '1234' in one place and '5678' in another).
October 2, 2009 at 3:56 pm
I'm going to guess that you're trying to build a dynamic SQL query.
See if the code below will give you what you're looking for.
Edit:
OOPS, I missed Lynn's comment.
I'll post my solution later if it's still required.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 2, 2009 at 5:28 pm
First, THANK YOU a lot guys for the extreme support.
The code I quoted is just one tenth of the original program.
My purpose is very straight forward and the database I involved is just for one simple reason, it is popular.
And it is NOT my original intention to "mislead" helpers to get the quick solution in terms of "FirstName", "LastName", this I have to apologize.
Any way I just want my dynamic sql query to run and the technique I could apply in order that SUBSTRING to activate when the sql is run, and now what I got is just the 'string name' (namely "FirstName").
Salute to ALL !!!
October 2, 2009 at 8:06 pm
Okay, your choice. We can just give you the answer and hope you learn something, or we can direct you to other resources so you can to some reading about Dynamic SQL and ask questions that will help you understand better what you are doing.
Which do you want??
October 2, 2009 at 8:09 pm
:Wow:Thank you.
I am greedy and I want both.
First for the project I am working and second for digging and learning more for the future.
Salute.
October 2, 2009 at 8:15 pm
Sorry, either or, not both. Please go for the latter and do some research first. I find that helps me learn and remember better than someone just handing me the answer.
October 2, 2009 at 8:27 pm
Thank you anyway, but I am still here waiting to receive more response(s).....
October 2, 2009 at 10:56 pm
And I'm still waiting for a well reasoned question that demonstrates you have taken the time to read about Dynamic SQL in Books Online.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply