Solved

SQL Query Help

Posted on 2012-04-02
7
261 Views
Last Modified: 2012-04-03
Team -

I have a table with a field "Information" in which i would like to get only the first character of all the words.

Example:
"Information" fields contains 5 rows

1. What is my name?
2. Ohio is a nice place.
3. Oh My God !!!
4. Job Well done..,
5. Hearty Welcome to our chairman.

I'd like to run a query which will pull only the first character of each word in each row.

i.e.,
1.WIMN
2.OIANP
3.OMG
4.JWD
5.HWTOC

Requesting your help please
0
Comment
Question by:davidi1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 37794910
Try:
SELECT left(field1,1), left(field2,1) from your_table_name
0
 
LVL 10

Expert Comment

by:Bawer
ID: 37794967
select SUBSTR(first_name,1,1)name from employee
0
 
LVL 15

Expert Comment

by:gplana
ID: 37795031
I think there isn't any function on SQL-Server for getting first character of every word. I think you need to create a user defined function for achieving this and then execute this query:

SELECT FirstCharOfEveryWord(Information) as first_characters
FROM yourTable;

Where FirstCharOfEveryWord is the function you should create. I suppose the idea is to take the first character (by SUBSTR function) and then use a loop to treat every character (gotten also by SUBSTR) and everytime you find a space take next character. But probably you should also treat things like if there are more than one space together, what to do with punctuation signs (point, comma, ...) and things like this, so this function could not be easy.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 18

Accepted Solution

by:
deighton earned 500 total points
ID: 37795083
do this using a function

-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE  FUNCTION fn_SentenceChars
(
	-- Add the parameters for the function here
	@SENTENCE varchar(200)
)
RETURNS varchar(200)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ANS  varchar(200)




DECLARE @I int;
SET @I = 1
WHILE @I < LEN(@SENTENCE)
BEGIN
	IF @I = 1
	BEGIN
	   SET @ANS = LEFT(@SENTENCE, 1);
	END
	ELSE
	BEGIN
		IF SUBSTRING(@SENTENCE, @I - 1, 1) =' ' AND SUBSTRING(@SENTENCE, @I, 1) <> ' '
				SET @ANS = @ANS + SUBSTRING(@SENTENCE, @I, 1); 	 
	END		
	SET @I = @I + 1;
END

-- Return the result of the function
RETURN @ANS



END
GO

Open in new window



then use it on your fields, here is a test I did

select dbo.fn_SentenceChars('the cat sat on the mat')

Open in new window

0
 
LVL 19

Expert Comment

by:Rimvis
ID: 37795277
Hi davidi1,

try this:

SELECT *, 
	(SELECT UPPER(SUBSTRING(Information, n, 1)) 
		FROM YourTable CROSS JOIN (SELECT row_number() over (order by object_id) AS n FROM sys.columns) AS num 
		WHERE ID = y.ID AND SUBSTRING(' ' + Information, n, 1) = ' ' AND n<LEN(Information) FOR XML PATH('')) 
FROM YourTable AS y

Open in new window

0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37796690
You are obviously doing this as part of another process.   I could not help wondering if you were implementing your own searching mechanism.  In which case, I would highly recommend you looked into implementing a full text index.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37798509
>> first character of all the words. <<

You have to define what you mean by "word".

Only spaces??

Me personally, I would think something along these lines, *conceptually* (not necessarily directly coded this way):

Find the first/next alpha/numeric char, to start a "word"
All immediately following: alpha,numeric, *and underscores* are part of that "word" (./|~ etc are not)
    First char that is NOT one of those ends the "word".
--Loop/repeat until all words are processed.


Is that right?  Or do you want include other chars within a "word"?
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question