SQL parse string

Question

I have SQL column in this format:

http://myServer/Lists/myform/DispForm.aspx?ID=6, RAB12EGH234 http://myServer/Lists/myform/DispForm.aspx?ID=5, CBTRR2345

I want to get only

RAB12EGH234 CBTRR2345

I used this:

select substring([FormName], charindex(',', ([FormName]), 0), 20) from [myDB].[dbo].[FormList]

However I get results like:

 , RAB12EGH234 , CBTRR2345

I don't want any space, I don't want comma but just the names. How do I edit my query? Thanks.

Andomar · Accepted Answer · 2013-05-10 15:22:06Z

2

Skip two extra characters, and use rtrim() to remove trailing spaces:

rtrim(substring(FormName, charindex(', ', FormName, 0) + 2, 20)) ^^^^^^

Example at SQL Fiddle.

edited May 10, 2013 at 15:22

answered May 10, 2013 at 15:08

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nemo Over a year ago

Thanks for your answer. It worked. In my e.g. above, I put 20 becuase I thought form Name would not be that long. But how do I make sure I get exact formName? no spaces or blanks after exact formName? FormName's length varies.

Andomar Over a year ago

You could add rtrim to remote spaces after the formname

Nemo Over a year ago

will that be same to this:(substring(FormName, charindex(', ', FormName, 0) + 2, LEN(FornName)) ?

Nemo Over a year ago

I m not comfy with fixed number 20 in my query. So I thought of getting exact length. Is this ok?

Andomar Over a year ago

The exact length is fine. Note that len() returns the length without trailing spaces, f.e. len(' ') = 0

|

Collectives™ on Stack Overflow

SQL parse string

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related