help needed

Question

Post reply

help needed

Vishal Singh

SSCertifiable

Points: 5830
More actions
November 19, 2010 at 5:56 am

#229438

Can somebody help writing a function to accept string value like this:
N'ABD',N'DYX',N'ALL'
And return the string like this
'ABD','DYX','ALL'
Appreciate the help.
-Forum Etiquette: How to post Performance Problems[/url]
-Forum Etiquette: How to post data/code to get the best help [/url]
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

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

You could use the replace function to remove N' and replace it with '

_______________________________________________________________

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/

Lowell SSC Guru Points: 323518 More actions · Answer 2

Sean Lange (11/19/2010)
You could use the replace function to remove N' and replace it with '

that's work pretty well, except for those rare cases when a variable between quotes ends with an N:

N'BAN',N'YES'

it'd be better to figure out what the OP is trying to do, and tackle it a different way.

it'd be dumb to split() the the string on the comma, replace each beginning char, and then concat it back together.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Can you elaborate on the acceptable formats of the string?

N'ABD',N'DYX',N'ALL' is OK

Is that the only format i.e hard coded letter N followed immediately by a quote, three letters, another quote followed by repeating sets with a single comma separating them?

If not, what about :

N'ABD', N'DYX',N'ALL' (extra space after first comma)

N 'ABD',N'DYX',N'ALL' (extra space after first N)

N 'ABD' , N 'DYX' , N 'ALL' N (lots of extra spaces and an extra N after the last quote)

N'A,BD',N'DYX',N'ALL' ( comma inside the first quoted string )

N'A,N''BD',N'DYX',N'ALL' ( comma N quote quote inside the first quoted string )

If any of those are acceptable inputs, can you tell us what all the variants of acceptable input are and how they should appear once processed.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

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

Lowell, how right you are. If the format is like the OP with no spaces we could replace ,N' with ,' and then get the right of that result like this.

declare @SillyString varchar(200) = 'N''ABD'',N''DYX'',N''BAN'''

set @SillyString = replace(@SillyString, ',N''', ',''')

select right(@SillyString, DATALENGTH(@SillyString) - 1)

_______________________________________________________________

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/

Lowell SSC Guru Points: 323518 More actions · Answer 5

depending on the data, for example if it was always N{quote}{three chars}{quote}{Comma}

you might just use STUFF and a tally table to for to the length of the variable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 6

Lowell (11/19/2010)
depending on the data, for example if it was always N{quote}{three chars}{quote}{Comma}
you might just use STUFF and a tally table to for to the length of the variable

Not always three fields but could be any number of fields. The field would always start with N' and ends with ' then a comma, Except the Ending field would not have a comma at end. for example:

N'ABC',N'DEF',N'GHI',N'JKL',N'MNO',N'PQRSTU',N'VX'

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

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

The simple piece I posted should work fine for that format too. Simple, clean, fast. 🙂

_______________________________________________________________

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/

Lowell SSC Guru Points: 323518 More actions · Answer 8

Sean Lange (11/19/2010)
The simple piece I posted should work fine for that format too. Simple, clean, fast. 🙂

gotta agree; clean fast code that takes into consideration everything wer've seen so far.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!