SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ignore comma in string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

paritosh
Starting Member

India
42 Posts

Posted - 12/16/2011 :  05:16:07  Show Profile  Reply with Quote
if i have a string a= '1,2,3,4,5'

and i want to ignore comma from this string and the desire result form is :-
1
2
3
4
5

what is the solution of this problem.

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 12/16/2011 :  05:23:22  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
You want this?

declare @a varchar(25)
Set @a= '1,2,3,4,5'

print replace(@a,',',Char(10))


Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

paritosh
Starting Member

India
42 Posts

Posted - 12/16/2011 :  05:29:49  Show Profile  Reply with Quote
quote:
Originally posted by senthil_nagore

You want this?

declare @a varchar(25)
Set @a= '1,2,3,4,5'

print replace(@a,',',Char(10))


Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008



thanks for this .

pls tell me about this line

print replace(@a,',',Char(10))

what is the purpose of char(10) in replace function.

thanks in advance.
Go to Top of Page

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 12/16/2011 :  05:35:18  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
Replace() function will replace ',' (comma) with char(10) (new line charchter)

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 12/16/2011 :  06:30:30  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
it isn't a new line character. it's a line feed.

If you are on windows (which you probably are!) then the proper way to end lines is {CARRIAGE RETURN}+{LINE FEED}

(it's different on unix btw)

So you should probably do

REPLACE(@a, ',', CHAR(13) + CHAR(10))


OP :

Did you really want only to display the results in printed format.

Or did you want to produce an output table by splitting the string?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

paritosh
Starting Member

India
42 Posts

Posted - 12/16/2011 :  06:57:02  Show Profile  Reply with Quote
quote:
Originally posted by Transact Charlie

it isn't a new line character. it's a line feed.

If you are on windows (which you probably are!) then the proper way to end lines is {CARRIAGE RETURN}+{LINE FEED}

(it's different on unix btw)

So you should probably do

REPLACE(@a, ',', CHAR(13) + CHAR(10))


OP :

Did you really want only to display the results in printed format.

Or did you want to produce an output table by splitting the string?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION





dear ,

if you have possible soulution so please give .
for select statement against print statement

thanks in advance
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 12/16/2011 :  07:07:07  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
it's generally referred to a a string splitting function. Basically it takes a string and returns a table valued type of the split string.

There are many, many, many different versions. My favourite uses a number table because it's very fast, while still being a vanilla sql function.

Check these links:
string splitting function discussion
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

number table:
http://www.sqlservercentral.com/articles/T-SQL/62867/

Whichever you choose the usage is more or less the same.

Say you have a table that looks like this:

DECLARE @myTable TABLE (
    [MyKey] INT PRIMARY KEY
    , [MyVal] VARCHAR(8000)
    )
INSERT @myTable ([MyKey], [MyVal])
      SELECT 1, 'a,b,c'
UNION SELECT 2, 'foo,bar'

Then you want to get results that look like:

MyKey | Pos | Value
------+-----+------------
1     | 1   | a
1     | 2   | b
1     | 3   | c
2     | 1   | foo
2     | 2   | bar


Then you'd use a SELECT statement like

SELECT
    a.[MyKey]
  , b.[Postion]
  , b.[Value]
FROM
    @MyTable AS a
    CROSS APPLY dbo.fnSplit(a.[MyVal]) AS b


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Edited by - Transact Charlie on 12/16/2011 07:08:12
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 12/16/2011 :  11:35:22  Show Profile  Reply with Quote
If you looking for good speed and you don't have strings longer than VARCHAR(8000) try the in-line tally-table version:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000