T-SQL Query

[lza]

Hello

I'm trying to use T-SQL for this query:
I want to list some columns from a table. The problem is that I want to cut some part of the output.
For example:
In the column 'name' I have following entries:
abcd
abcd:aaaa
abcde:aaaa
I would like to receive only the part left to the ':', in this case:
abcd
abcd
abcde

Is this possible?

Regards
Leandro

 

[scriverb]

Try this:

ShortenedName: Mid$(me![Name], 1, Instr(1, me![Name], ":&quot-1)

This should do it for you. Bob Scriver

 

[lza]

Thank you for your quick answer!

The Query Analyser says "Incorrect syntax near '!'".
Maybe I'm not using the right query syntax (I'm not very experienced...).
Can you give me the full query statament?
Thanks
Leandro

 

[JeremyNYC]

First-off, change the name of your field from Name to something else. Name is an Access reserved word, and you will receive pain in this life and all those to come if you continue to use that as the name of anything in Access.

Next, try this:
SELECT SomethingElse, Mid$([SomethingElse], 1, Instr(1, [SomethingElse], ":&quot-1) as NewSomethingElse FROM tblWhatever

That will do it in a select query. You may want to modify it to update a new field or something else, but that should get you started.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[scriverb]

Thanks JeremyNYC. I was away from my PC for a while to get back to his posting. Iza, Jeremy has given you the SQL that you can use after you update the correct field and tables names in it to test out the new column. I just gave you the code to be used in the ACCESS design grid to insert into a new column of an existing query. His SQL statement will work just fine.

If you need more just get back with us. Bob Scriver

 

[JeremyNYC]

Bob,

I'd like to drop you an e-mail, if you don't mind. Thought I'd ask here first.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[scriverb]

No problem Jeremy. Check my profile for address. Bob Scriver

 

[tlbroadbent]

lza ,

This is an Access JET SQL forum. You should post T-SQL questions in the SQL Server forum - forum183. The T-SQL syntax follows.

Select
Case
When charindex(':', [Name])>0
Then Left([Name], Charindex(':', [Name])-1)
Else [Name]
End As ShortName
From TableName If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[scriverb]

tlbroadbent: Boy, I have to begin reading the first question a little better next time. I completely missed the T-SQL statement. Thanks for setting that one straight. Bob Scriver

 

[lza]

Hello Everybody!

Sorry about the wrong Forum...

Terry's solution works perfect!
Thank you very much!

Regards
Leandro