Query Help

Question

Post reply

Query Help

sqlnaive

SSCoach

Points: 17437
More actions
June 29, 2011 at 11:20 pm

#104076

Hello Folks, I am trying to write a function. If input is like AAAA, pqrstuv, xxxx-lmn, ZZZsswD then output should be 'AAAA', PQRSTUV', 'XXX-LMN', 'ZZZSSWD'. Any suggestions ?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 1

SQL Server has a built-in function to do this:

select UPPER('sql server')

Which will yield:

SQL SERVER

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

sqlnaive SSCoach Points: 17437 More actions · Answer 2

Thanks Nakul. Thats not what I'm looking for. Lolz.

Actually looking for "If input string is like AAAA, pqrstuv, xxxx-lmn, ZZZsswD then output string should be 'AAAA', PQRSTUV', 'XXX-LMN', 'ZZZSSWD'".

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

sqlnaive (6/30/2011)
Thanks Nakul. Thats not what I'm looking for. Lolz.
Actually looking for "If input string is like AAAA, pqrstuv, xxxx-lmn, ZZZsswD then output string should be 'AAAA', PQRSTUV', 'XXX-LMN', 'ZZZSSWD'".

The 'words' of the input string are unquoted, mixed-case and delimited.

The 'words' of the output string are upper-cased, mostly quoted, and delimited - and different: xxxx-lmn <> 'XXX-LMN'. What are you trying to do?

^{“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

sqlnaive SSCoach Points: 17437 More actions · Answer 4

I just want to input a string separated by comma and process it to give me output with same string separated by comma BUT having quoted each word so that i can put that string in the IN() query.

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

Like this?

DECLARE @string AS VARCHAR(50)

SET @string = 'AAAA, pqrstuv, xxxx-lmn, ZZZsswD'

SELECT '''' + REPLACE(REPLACE(UPPER(@string),' ',''),',',''',''') + ''''

-Edit-

Bear in mind that this will not work if there are ever spaces in the values in the string, e.g. "E ast, is, eAsy" would be translated to "'EAST','IS','EASY'"

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sqlnaive SSCoach Points: 17437 More actions · Answer 6

yes, Perfect. Thats what I was looking for. Thanks once again.

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

If you want a more robust delimited string parser check out this article[/url] from Jeff Moden.

_______________________________________________________________

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/