SQL Query Help

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
LVL 2
davidi1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:
Try:
SELECT left(field1,1), left(field2,1) from your_table_name
BawerCommented:
select SUBSTR(first_name,1,1)name from employee
gplanaCommented:
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.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

deightonprogCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RimvisCommented:
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

SJCFL-AdminCommented:
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.
Scott PletcherSenior DBACommented:
>> 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"?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.