Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Be prepared to lose once in a while
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

30
March

How Do I Find the Position of a Specific Character in a String Field using SQL?

I was recently asked during a SQL Reporting Services training session, how does one find a position of a specified character in a string field, in the SQL Query.

Solution

To find the position of a specified character in a string field, one can use the function CHARINDEX.

The CHARINDEX function has 3 arguments:
  • Search Text
  • String to be searched
  • Starting Position


To find the first space position in a string field:

CHARINDEX( ' ', {Tablename.Fieldname}, 1)


To find the second space position in a string field:

CHARINDEX(' ', {Tablename.Fieldname},1+CHARINDEX(' ', {Tablename.Fieldname},1))


To find the first space postion starting from the right of a string field:

Unfortunately there is not an SQL function to directly extract characters in a string field, starting from the right.  To get around this one can use the REVERSE function

CHARINDEX(' ',REVERSE({Tablename.Fieldname}),1)



If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us
1

Smithe769

02
June
This actually answered my drawback, thank you!
2

John

11
June
Very nice site!
3

Johnny O

02
October
If you were asked in a SQL Reporting Services training session, was the question directed toward an SQL Query to obtain the answer or in SSRS itself. The above answers the question in an SQL Query, but not directly in SSRS. There is no CharIndex function within SSRS.

MAKE A COMMENT

Name *

Email Address *

Comment *

To prove you are human what is:

29 + 85 =