Solved

SQL Query Help

Posted on 2012-04-02
7
257 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

810 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