Solved

SQL Query Help

Posted on 2012-04-02
7
258 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
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

839 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