CONCATENATE not working

Question

CONCATENATE not working

Krasavita

SSC-Insane

Points: 23138
More actions
August 30, 2011 at 7:08 am

#390302

Hi, can someone please help me with this syntax, it is not working in sql server
LEFT(TRIM(CONCATENATE(MID(prefix,7,4), phone)),20)
Thank you

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

"Concatenate" is a Denali feature as far as I know. Doesn't exist yet in SQL 2008.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

Krasavita

SSC-Insane

Points: 23138

August 30, 2011 at 7:20 am

#1374971

What can I use instead?

Thank you

GSquared SSC Guru Points: 260824 More actions · Answer 3

The plus symbol works.

LEFT(LTRIM(MID(prefix,7,4) + phone),20)

I also noticed you have "TRIM" as a function in there. No such function in current T-SQL. Try "LTRIM" and/or "RTRIM" instead.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

This is Oracle code.

CONCATENATE and TRIM are Oracle functions.

Is this a migrated database?

-- Gianluca Sartori

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Looks like Excel. Try this:

LEFT(LTRIM(RTRIM(SUBSTRING(prefix,7,4) + phone)),20)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Krasavita SSC-Insane Points: 23138 More actions · Answer 6

Thank you very much

I get error message

Incorrect syntax near ')' and MID is not recognized function

can we use MID there?

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

MID is not a sql function either. You will have to use substring instead.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Krasavita SSC-Insane Points: 23138 More actions · Answer 8

Thank you, the code is working but not 100%

Here is my data 794546,794548,794549

Results I get:4 794546,794548,7945

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Krasavita (8/30/2011)
Thank you, the code is working but not 100%
Here is my data 794546,794548,794549
Results I get:4 794546,794548,7945

So given that input what are you looking for as output? You first example sounded like a phone number but this looks nothing like a phone number.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Krasavita SSC-Insane Points: 23138 More actions · Answer 10

Sorry,

LEFT(LTRIM(RTRIM(SUBSTRING(prefix,7,4) + phone)),20)

If I get prefix null,this formula won't work,how can I work around it?

Thank you

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

How about some ddl and a few sample rows with expected output? It should be pretty simple but it is a LOT easier on us if we have something to work on so we don't have create tables and data first.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Probably like this

SELECT LEFT(LTRIM(RTRIM(SUBSTRING(ISNULL(prefix,''),7,4) + phone)),20)

FROM (

SELECT Prefix = ' 0151', phone = '7217164' UNION ALL

SELECT NULL, '7217164'

) [sample]

but it depends on the shape of your data and what you wish to see in your output.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

GSquared SSC Guru Points: 260824 More actions · Answer 13

Krasavita (8/30/2011)
Sorry,
LEFT(LTRIM(RTRIM(SUBSTRING(prefix,7,4) + phone)),20)
If I get prefix null,this formula won't work,how can I work around it?
Thank you

Put IsNull or Coalesce around the prefix.

LEFT(LTRIM(RTRIM(SUBSTRING(isnull(prefix, ''),7,4) + phone)),20)

That will replace a null prefix with a zero-length string, and then add the phone to that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon